Using DBMS_REDEFINITION package to reorganize tables online

Tweet

I needed to partition a table while it was accepting updates. So I decided to use DBMS_REDEFINITION package provide by Oracle.



The following privileges must be granted to the user:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following privileges may be required too:
CREATE ANY INDEX
CREATE ANY TRIGGER

First of all you have to create an interim table with the same columns and data type of the table to be redefined.

Anyway I used SYS user to perform the following steps:
alter session force parallel dml parallel 3;
alter session force parallel query parallel 3;


Next step is to check if table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table('SMS', 'SMS_TRANSACTION');


With no errors displayed, I can proceed starting the redefintion using the following command. This operation can take quite some time to complete, but any queries and DML are available on table being redefined during the entire process:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;


The following command copies dependent objects, so it automatically create any triggers, indexes, materialized view logs, grants, and constraints on our interim table:
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SMS', 'SMS_TRANSACTION','INT_SMS_TRANSACTION',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
dbms_output.put_line(num_errors);
END;


At this point you can query DBA_REDEFINITION_ERRORS view to check for blocking errors. You have to correct these errors and then again reexecute
the COPY_TABLE_DEPENDENTS procedure above:
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

The next optional step points to resynchronize the interim table before building any constraints and indexes, especially if you think there was delay between the completion of the previous operation and moving on to finish the redefinition.
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;


Next you can execute the following command to complete the redefinition:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SMS', 'SMS_TRANSACTION', 'INT_SMS_TRANSACTION');
END;


Finally you should gather new statistic for your "new" table:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SMS',
tabname => 'SMS_TRANSACTION',
estimate_percent => 45
);
end;