Donald K. Burleson Oracle Tips

Oracle9i online table redefinition

Prior to Oracle9i table redefinition was only normally performed with export/import, and this meant that the table was offline during the process. Even online changes (“alter table add new_col number(3)”) cause exclusive locks, preventing all DML (inserts, updates) until the DDL has completed.

To solve this problem Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.

The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the table, enqueue changes during the redefinition, and then re-synchronize the restructured table with the changes that have accumulated during reorganization.

This is a very important feature for 24x7 Oracle databases because the DBA now has the ability to restructures tables while they remain available for update.

If your reorganization fails, you must take special steps to make it re-start.  Because the redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to release the snapshot to re-start you procedure.

The ‘dbms_redifinition.abort_redef_table’ procedure which accepts 3 parameters (schema, original table name, holding table name), and which “pops the stack” and allows you to start over.

dbms_redefinition.abort_redef_table(‘PUBS’,’TITLES’,’TITLES2’);

The 9i redefinition procedures has some restrictions:

• There must be enough space to hold two copies of the table.

• Primary key columns cannot be modified.

• Tables must have primary keys.

• Redefinition must be done within the same schema.

• New columns added cannot be made NOT NULL until after the redefinition operation.

• Tables cannot contain LONGs, BFILEs or User Defined Types.

• Clustered tables cannot be redefined.

• Tables in the SYS or SYSTEM schema cannot be redefined.

• Tables with materialized view logs or materialized views defined on them cannot be redefined.

•  Horizontal sub setting of data cannot be performed during the redefinition.

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.