Step by step to use online redefinition

Step1. Check whether the table can be redefined

exec DBMS_REDEFINITION.CAN_REDEF_TABLE(user,’CC’,dbms_redefinition.cons_use_rowid);
–should no error

Step2. Create new table

CREATE TABLE “CC_NEW” (
“ID” NUMBER NOT NULL,
“NODE_ID” NUMBER ,
“COLLECTION_ID” NUMBER ,
“NAME” VARCHAR2(200) ,
“VALUE” CLOB,
“LAST_UPDATE” DATE
);

Step3. Start redefine

exec DBMS_REDEFINITION.START_REDEF_TABLE(user, ‘CC’,’CC_NEW’,’ID ID, NODE_ID NODE_ID,
COLLECTION_ID COLLECTION_ID, NAME NAME, TO_CLOB(VALUE) VALUE,LAST_UPDATE LAST_UPDATE’,dbms_redefinition.cons_use_
rowid);
var error_count number;
exec dbms_redefinition.copy_table_dependents(user, ‘CC’, ‘CC_new’,1, true,true,true,false,:error_count);
print :error_count;
–should be 0

Step4. Sync table. You can do it as many times as you want.

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, ‘CC’, ‘CC_NEW’);

Step5. Finish redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, ‘CC’, ‘CC_NEW’);

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: