Skip to content
January 29, 2016 / doganay

CONVERT NON-PARTITIONED TABLE TO PARTITIONED TABLE ONLINE

BEGIN
 DBMS_REDEFINITION.CAN_REDEF_TABLE ('MYUSER1','SOURCE_TABLE', DBMS_REDEFINITION.CONS_USE_PK);
--rowid can be used if no pk exists on source table:
--DBMS_REDEFINITION.CAN_REDEF_TABLE ('MYUSER1','SOURCE_TABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE('MYUSER', 'SOURCE_TABLE', 'DEST_TABLE');
END;
/

--rowid can be used if no pk exists on source table:
BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE(
 uname => 'MYUSER',
 orig_table => 'SOURCE_TABLE',
 int_table => 'DEST_TABLE',
 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/


--be careful with table dependents.
--when converting your table to partitioned, you probably want your users to be local, not global.
--so, it's a better way to not to copy table dependents, you can create them manually later
DECLARE
 num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('MYUSER', 'SOURCE_TABLE', 'DEST_TABLE', DBMS_REDEFINITION.CONS_ORIG_PARAMS,
                                           TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/


--this is the sync part, you can run this whenever and how many you want:
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYUSER', 'SOURCE_TABLE', 'DEST_TABLE');


--this is the final part, tables are being exchanged:
BEGIN
 DBMS_REDEFINITION.FINISH_REDEF_TABLE ('MYUSER', 'SOURCE_TABLE', 'DEST_TABLE');
END;
/

Advertisements

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: