Online Table Redefinition

Online Table Redefinition

9i allows you to redefine a table online. Users can issue DML against the table during most of the online redefinition. For online table redefinition, the table:

• Must have a primary key, and source and target tables for redefinition must have the same primary key column
• Can not contain BFILE or LONG columns, user-defined types, or be part of a cluster or be an overflow table for an IOT
• Can not belong to SYS or SYSTEM schemas, or have materialized views or materialized view logs defined on it

Here is the process of online table redefinition:

1. Run the Oracle procedure DBMS_REDEFINITION.CAN_REDEF_TABLE to verify that the table meets the above conditions and can be redefined online
2. Create an interim table with the same characteristics and in the same schema as the table to redefine
3. Run DBMS_REDEFINITION.START_REDEF_TABLE to start the redefinition process
4. Create indexes, constraints, triggers, and grants with different names on the interim table. DISABLE all constraints (Oracle automatically ENABLEs them for you later).
5. If a large number of DML updates are going against the original table, sync it with the interim table by running DBMS_REDEFINITION.SYNC_INTERIM_TABLE.
6. Complete the redefinition by running DBMS_REDEFINITION.FINISH_REDEF_TABLE. The table is briefly locked in exclusive mode as the original table is redefined with all the characteristics, indexes, constraints, triggers, and grants that you defined on the interim table. Constraints are automatically enabled.

Note that if any problem occurs during the redefinition process, you must run DBMS_REDEFINITION.ABORT_REDEF_TABLE to terminate the redefinition process.