First, check that your user is eligeble for usage of the DBMS_REDEFINITION package. It requires
generous privileges.
Step 1: determine if the table can be reorganized:
whenever sqlerror exit
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept move_method prompt 'Move method (cons_use_pk or cons_use_rowid): '
set serveroutput on
EXEC DBMS_REDEFINITION.can_redef_table('&&owner','&&table_name',dbms_redefinition.&&move_method);
exit
Step 2: Create an interim table with the desirable layout:
If the outcome of step one was positive, you can proceed. You can for example create the interim table partitioned, while the original table is a normal table without partitions.
Important:
If you copy/paste DDL from SQL Developer, TOAD or similar tools, and use this as a template for your interim table, make sure *all* columns allow NULL values.
Otherwise, you will receive errors when running the COPY_TABLE_DEPENDENTS procedure in step 4
set serveroutput on
set feedback off
set verify off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table_name prompt 'Interim table name: '
accept move_method prompt 'Move method (cons_use_pk or cons_use_rowid): '
whenever sqlerror exit
set feedback off
set verify off
begin
DBMS_REDEFINITION.start_redef_table(uname=>'&&owner',
orig_table=>'&&table_name',
int_table=>'&&interim_table_name',
col_mapping=>NULL,
options_flag=>dbms_redefinition.&&move_method);
end;
/
exit
Step 4: Copy the dependent object to the interim table
If you want the indexes from the original table to be copied over along with triggers, constraints etc, use the directive
copy_indexes=>dbms_redefinition.cons_orig_params
If you prefer creating the indexes manuall after you have finished the redefinition, use
whenever sqlerror exit
set serveroutput on
set feedback off
set verify off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'&&owner',
orig_table=>'&&table_name',
int_table=>'&&interim_table',
-- copy_indexes=>dbms_redefinition.cons_orig_params,
copy_indexes=>0,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
exit
Step 5: synchronize the interim table with potential intermittent transactions:
whenever sqlerror exit
set serveroutput on
set feedback off
set verify off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>'&&owner',orig_table=>'&&table_name',int_table=>'&&interim_table');
end;
/
exit
Step 6: finish the redefinition:
whenever sqlerror exit
set serveroutput on
set feedback off
set verify off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'&&owner',
orig_table=>'&&table_name',
int_table=>'&&interim_table');
end;
/
exit
At any time before the execution of FINISH_REDEF_TABLE, you can chose to abort the procedure by calling the
dbms_redefinition.abort_redef_table procedure:
whenever sqlerror exit
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
set serverout on
exec dbms_redefinition.abort_redef_table(uname=>'&&owner',orig_table=>'&&table_name',int_table=>'&&interim_table');
exit
Tip: when you create your interim table, give it a short name, like MYTABLE.
I have had cases where longer names, even though they are below the 30 character limit, will create trouble during the execution of the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure.
Errors during the redefinition can be seen from the DBA_REDEFINITION_ERRORS view:
SET LONG 2000
SET LINES 300
COL OBJECT_TYPE FORMAT A20
COL OBJECT_OWNER FORMAT A10
COL OBJECT_NAME FORMAT A20
COL BASE_TABLE_NAME FORMAT A20
COL DDL_TXT FORMAT A50
COL ERR_TXT FORMAT A100
SELECT OBJECT_TYPE, OBJECT_OWNER, OBJECT_NAME,BASE_TABLE_NAME,DDL_TXT, ERR_TXT
FROM DBA_REDEFINITION_ERRORS;
