Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Online Table Redefinition Enhancements in Oracle Database 10g Release 1

Online table redefition 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.

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 AS
SELECT * 
FROM   test.redef_tab WHERE 1=2;

-- 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 the Top.