Online Table Redefinition Enhancements in Oracle Database 11g Release 1

Online Table Redefinition Enhancements in Oracle Database 11g Release 1

By default, online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.
To see this new behavior, first me must create a table with some dependent objects.

CONN test/test@db11g

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;

CREATE SEQUENCE redef_tab_seq;

CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  redef_tab.id%TYPE,
  p_description OUT redef_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   redef_tab
  WHERE  id = p_id;
END;
/

CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/

If we check the status of the schema objects we can see that all of them are valid.

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
REDEF_TAB            TABLE               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_V          VIEW                VALID

6 rows selected.

SQL>

Now we perform an online table redefinition.

CONN sys/password@db11g 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');

-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); 

-- Add new PK.
ALTER TABLE test.redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');

-- Remove original table which now has the name of the new table
DROP TABLE test.redef_tab2;

-- Rename the primary key constraint.
ALTER TABLE test.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk;

Finally, we re-check the status of the schema objects.

CONN test/test@db11g

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
REDEF_TAB_SEQ        SEQUENCE            VALID
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB_V          VIEW                VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB            TABLE               VALID

5 rows selected.

SQL>

Notice that the GET_DESCRIPTION procedure and REDEF_TAB_V view are still valid, but the REDEF_TAB_BIR trigger is gone. The trigger was still associated with the original table, renamed to REDEF_TAB2, so when the original table was dropped, the trigger was dropped with it.
In addition to the dependency changes, the COPY_TABLE_DEPENDENTS procedure can now also copy statistics and materialized view logs.