Online Table Redefinition

Hands On

set timing on

alter session force parallel dml parallel;
alter session force parallel query parallel;

grant CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE,CREATE ANY TRIGGER,CREATE ANY INDEX to SCOTT;

set long 10000
set pagesize 0
select dbms_metadata.get_ddl('TABLE','TBLMYREL') from dual;
select dbms_metadata.get_dependent_ddl('INDEX','TBLMYREL') from dual;

CREATE TABLE TBLMYREL_PART
   (
   SID VARCHAR2(256 BYTE) NOT NULL ENABLE,
   ID       NUMBER(*,0) NOT NULL ENABLE,
   COLUMN1  TIMESTAMP (6),
   COLUMN1  TIMESTAMP (6),
   COLUMN1  CHAR(1 BYTE) DEFAULT '0',
   COLUMN1  NUMBER(*,0),
   COLUMN1  NUMBER(20,0),
   COLUMN1  VARCHAR2(256 BYTE),
   COLUMN1  VARCHAR2(1024 BYTE),
   COLUMN1  TIMESTAMP (6),
   COLUMN1  TIMESTAMP (6),
   COLUMN1  NUMBER(5,0),
   COLUMN1  TIMESTAMP (6),
   COLUMN1 VARCHAR2(256 BYTE),
   COLUMN1 VARCHAR2(256 BYTE)
   )
PARTITION BY HASH  (SUBSCRIBERID)
(PARTITION prt1        tablespace tbs_1,
 PARTITION prt2        tablespace tbs_2,
 PARTITION prt3        tablespace tbs_3,
 PARTITION prt4        tablespace tbs_4,
 PARTITION prt5        tablespace tbs_5,
 PARTITION prt6        tablespace tbs_6,
 PARTITION prt7        tablespace tbs_7,
 PARTITION prt8        tablespace tbs_8
);

Table created.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('SCOTT','TBLMYREL');
BEGIN DBMS_REDEFINITION.can_redef_table('SCOTT','TBLMYREL'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."TBLMYREL"
with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627
ORA-06512: at line 1


SQL> alter table SCOTT.TBLMYREL add primary key(ID);

Table altered.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('SCOTT','TBLMYREL');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.start_redef_table('SCOTT','TBLMYREL','TBLMYREL_PART');

PL/SQL procedure successfully completed.

CREATE INDEX SCOTT.IDX1 ON SCOTT.TBLMYREL_PART (SID);

CREATE UNIQUE INDEX SCOTT.IDX2 ON SCOTT.TBLMYREL_PART (ID);

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('SCOTT','TBLMYREL','TBLMYREL_PART');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM SCOTT.TBLMYREL;

  COUNT(1)
----------
   4104644

SQL> SELECT COUNT(1) FROM SCOTT.TBLMYREL_PART;

  COUNT(1)
----------
   4104644

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('SCOTT','TBLMYREL','TBLMYREL_PART');

PL/SQL procedure successfully completed.

You can cross check the DDL
   select dbms_metadata.get_dependent_ddl('INDEX','TBLMYREL') from dual;
   select dbms_metadata.get_dependent_ddl('INDEX','TBLMYREL_PART') from dual;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.