Online table redefinition was introduced in Oracle 9i as part of a range of new high availability features. Oracle 10g Release 1 improves the ease of use of online table redefintions by adding the COPY_TABLE_DEPENDENTS
procedure to the DBMS_REDEFINITION
package. This new procedure optionally copies all indexes, grants, triggers, constraints and privileges from the source table to the interim table.
All referential integrity constraints cloned by the procedure are created in a disabled state, then automatically enabled after the redefinition is complete. The triggers cloned to the interim table are disabled until the redefinition is completed. Once the redefinition is complete, all cloned objects are renamed to the original names used by they objects they were cloned from.
Related articles.
To see this in action, first we must create a table with some dependent objects.
CONN test/test CREATE TABLE redef_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT redef_tab_pk PRIMARY KEY (id) ); CREATE INDEX redef_tab_desc_i ON redef_tab(description); CREATE SEQUENCE redef_tab_seq; CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT redef_tab_seq.NEXTVAL INTO :new.id FROM dual; END; /
The following query displays the schema objects and their status.
COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- REDEF_TAB_DESC_I INDEX VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_PK INDEX VALID REDEF_TAB TABLE VALID REDEF_TAB_BIR TRIGGER VALID SQL>
Now we perform an online table redefinition.
CONN / AS SYSDBA -- Check table can be redefined EXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB'); -- Create new table CREATE TABLE test.redef_tab2 ( id NUMBER, description VARCHAR2(50) ); -- Alter parallelism to desired level for large tables. --ALTER SESSION FORCE PARALLEL DML PARALLEL 8; --ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8; -- Start Redefinition EXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Copy table dependents SET SERVEROUTPUT ON DECLARE l_num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.copy_table_dependents( uname => 'TEST', orig_table => 'REDEF_TAB', int_table => 'REDEF_TAB2', copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default copy_triggers => TRUE, -- Default copy_constraints => TRUE, -- Default copy_privileges => TRUE, -- Default ignore_errors => FALSE, -- Default num_errors => l_num_errors); DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors); END; / l_num_errors=0 PL/SQL procedure successfully completed. SQL> -- Display schema contents, notice the new objects. COLUMN object_name FORMAT A25 SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------- ------------------- ------- REDEF_TAB TABLE VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_DESC_I INDEX VALID REDEF_TAB2 TABLE VALID MLOG$_REDEF_TAB TABLE VALID RUPD$_REDEF_TAB TABLE VALID TMP$$_REDEF_TAB_PK0 INDEX VALID TMP$$_REDEF_TAB_DESC_I0 INDEX VALID REDEF_TAB_BIR TRIGGER VALID TMP$$_REDEF_TAB_BIR0 TRIGGER VALID SQL> -- Optionally synchronize new table with interim data before index creation EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Complete redefinition EXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Display schema contents, notice the object name changes. COLUMN object_name FORMAT A25 SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------- ------------------- ------- REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB TABLE VALID REDEF_TAB2 TABLE VALID REDEF_TAB_DESC_I INDEX VALID TMP$$_REDEF_TAB_DESC_I0 INDEX VALID REDEF_TAB_PK INDEX VALID TMP$$_REDEF_TAB_PK0 INDEX VALID TMP$$_REDEF_TAB_BIR0 TRIGGER INVALID REDEF_TAB_BIR TRIGGER INVALID SQL> -- Remove original table which now has the name of the new table DROP TABLE test.redef_tab2; -- Display schema contents, notice the trigger status. COLUMN object_name FORMAT A25 SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------- ------------------- ------- REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB TABLE VALID REDEF_TAB_DESC_I INDEX VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_BIR TRIGGER INVALID SQL> -- Recompile the trigger ALTER TRIGGER test.redef_tab_bir COMPILE;
If there are any errors during the call to the COPY_TABLE_DEPENDENTS
procedure you should fix the cause, then call the procedure again.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1