Online Table Reorganization

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.  The online reorganization packages do this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the "Create table as select" command:

Here is a simple execution of an online table reorganization:

• -- Check table can be redefined

EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMPLOYEES');

• -- Create new table with CTAS

CREATE TABLE scott.employees2

TABLESPACE tools AS

SELECT empno, first_name, salary as sal

FROM employees WHERE 1=2;

• -- Start Redefinition

EXEC Dbms_Redefinition.Start_Redef_Table( -

  'SCOTT', -

  'EMPLOYEES', -

  'EMPLOYEES2', -

  'EMPNO EMPNO, FIRST_NAME FIRST_NAME, SALARY*1.10 SAL);

• -- Optionally synchronize new table with interim data 

EXEC dbms_redefinition.sync_interim_table( -

  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');

• -- Add new keys, FKs and triggers

ALTER TABLE employees2 ADD

(CONSTRAINT emp_pk2 PRIMARY KEY (empno)

USING INDEX

TABLESPACE indx);

• -- Complete redefinition

EXEC Dbms_Redefinition.Finish_Redef_Table( -

  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2'); 

• -- Remove original table which now has the name of the new tableDROP TABLE employees2;

Online Redefinition

In a very active database environment, it sometimes becomes necessary to modify a hot table to improve the performance of queries and DML statements. To achieve this, Oracle provides a mechanism called online redefinition.

When a table is redefined online, it is accessible to DML during much of the redefinition process. However, the table is locked in the exclusive mode only during a very small period. This window or period is usually independent of the size of the table and the complexity of the redefinition.

New Additions and Changes to dbms_redefinition

In Oracle, there were many restrictions to performing redefinition. It was even necessary to create dependent objects, such as constraints and indexes, manually. Oracle Database 10g introduces many enhancements to facilitate easier administration and to minimize manual tasks. They are discussed next.

Support for additional data types, such as CLOB and BLOB has been introduced. Tables containing character LOBS and binary LOBS can now be redefined online. However, tables with BFILE columns cannot be redefined online.

Tables that are involved in master-master replication can undergo redefinition.  However, there is a restriction that no horizontal or vertical sub-setting, or column transformations, are allowed.

Three new procedures are added to the package dbms_redefinition . They are as follows:

• - copy_table_dependents   - clones the dependent objects of the table being redefine

• -  register_dependent_object - registers a dependent object on the table being redefined and on the interim table 
• -  unregister_dependent_object  - un-registers a dependent object on the table being redefined and on the interim table.

The following constants are also introduced in specifying the type of the dependent object.

• -  cons_constraint   - specifies the dependent object type CONSTRAINT.

• -  cons_index  - specifies the dependent object type is of type Index.

• -  cons_trigger  - specifies the dependent object type is of TRIGGER

• -  copy_orig_params  - specifies that the indexes on the original table be copied using the storage parameters of the original.

•  A new optional parameter, orderby_cols , has been introduced for the start_redef_table  procedure.

You can use this parameter to specify the columns that should be used to order the rows during the initial instantiate of the interim table. 

In addition, you can also use the optional key word ASCENDING or DESCENDING.

• The remaining restrictions are shown below:

-  The table to be redefined cannot be part of a cluster.

-  Tables in the SYS and SYSTEM schemas cannot be online redefined.

-  Temporary tables cannot be redefined.

-  Tables with BFILE columns cannot be online redefined.

- The overflow table of an index-organized table cannot be online redefined.

-  Tables with user-defined types (objects, REFs, collections, typed tables) cannot be redefined online.

-  Table redefinition cannot be done NOLOGGING.

 ****************************** ****************************** ***********************************



Let's repeat the redefinition, but this time including a call to the COPY_TABLE_DEPENDENTS procedure. 
In this case we are not manually recreating the primary key constraints as this will be done by the procedure call.

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;

-- 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'); 

• -- Optionally synchronize new table with interim data before index creation

EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');  

• -- Copy 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        => 1,             -- Default

    copy_triggers       => TRUE,          -- Default

    copy_constraints    => TRUE,          -- Default

    copy_privileges     => TRUE,          -- Default

    ignore_errors       => FALSE,         -- Default

    num_errors          => l_num_errors,

    copy_statistics     => FALSE,         -- Default

    copy_mvlog          => FALSE);        -- Default

  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 

END;

/

• -- 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

 DROP TABLE test.redef_tab2;

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 ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS

-------------------- ------------------- -------

GET_DESCRIPTION      PROCEDURE           VALID

REDEF_TAB            TABLE               VALID

REDEF_TAB2_PK        INDEX               VALID

REDEF_TAB_BIR        TRIGGER             INVALID

REDEF_TAB_SEQ        SEQUENCE            VALID

REDEF_TAB_V          VIEW                INVALID

6 rows selected.

SQL>

This time the trigger has not been lost, as it was cloned by the COPY_TABLE_DEPENDENTS procedure. Notice the trigger and view are both marked as invalid now. 

• They can be recompiled as follows.

ALTER TRIGGER redef_tab_bir COMPILE;

ALTER VIEW redef_tab_v COMPILE;

COLUMN object_name FORMAT A20

SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS

-------------------- ------------------- -------

GET_DESCRIPTION      PROCEDURE           VALID

REDEF_TAB            TABLE               VALID

REDEF_TAB2_PK        INDEX               VALID

REDEF_TAB_BIR        TRIGGER             VALID

REDEF_TAB_SEQ        SEQUENCE            VALID

REDEF_TAB_V          VIEW                VALID

6 rows selected.