This blog is intended for DBA’s who would like to reorganize tables online using Oracle dbms_redefinition package.
Issue:
DBAs may need to reorganize tables to improve the SQL query performance or to reclaim unused space. There are many ways to do a reorg a table. This blog post demonstrates doing the reorg ONLINE using DBMS_REDIFINATION procedure.
Solution
STEP-1:
Determine if a given table can be redefined online. If the table is not a candidate for online redefinition, an error message is raised.
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(‘XXUSER’,’XXCOMP_PRODUCT_PENDING_DETAIL’,DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
STEP-2:
Create an interim table(XXCOMP_PRODUCT_PENDING_DETAIL_INTR) . Get the DDL of the Original table, rename the tablename and execute the DDL.
SQL> SELECT DBMS_METADATA.get_ddl (‘TABLE’, ‘XXCOMP_PRODUCT_PENDING_DETAIL’, ‘XXUSER’) from dual;
STEP-3:
Start the table redefinition process. Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be redefined)
SQL> begin
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => ‘XXUSER’,
orig_table => ‘XXCOMP_PRODUCT_PENDING_DETAIL’,
int_table => ‘XXCOMP_PRODUCT_PENDING_DETAIL_INTR’,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/
PL/SQL procedure successfully completed.
STEP-4:
Copy dependent objects. This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table.
SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(‘XXUSER’,’XXCOMP_PRODUCT_PENDING_DETAIL’,’XXCOMP_PRODUCT_PENDING_DETAIL_INTR’, DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
dbms_output.put_line(‘Errors :’||num_errors);
END;
/
Errors :0
PL/SQL procedure successfully completed.
STEP-5:
Synchronize the interim table. This procedure keeps the interim table synchronized with the original table.
SQL> begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE (
uname => ‘XXUSER’,
orig_table => ‘XXCOMP_PRODUCT_PENDING_DETAIL’,
int_table => ‘XXCOMP_PRODUCT_PENDING_DETAIL_INTR’);
end;
/
PL/SQL procedure successfully completed.
STEP-6:
Complete the redefinition. This procedure completes the redefinition process.
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘XXUSER’,’XXCOMP_PRODUCT_PENDING_DETAIL’,’XXCOMP_PRODUCT_PENDING_DETAIL_INTR’);
PL/SQL procedure successfully completed.
STEP-7:
Drop the interim table.
SQL> DROP TABLE XXUSER.XXCOMP_PRODUCT_PENDING_DETAIL_INTR;
Table dropped.
