DBMS_REDEFINITION

ABORT_REDEF_TABLE Procedure

This procedure cleans up errors that occur during the redefinition process. This procedure can also be used to terminate the redefinition process any time after the START_REDEF_TABLE Procedure has been called and before the FINISH_REDEF_TABLE Procedure is called.

DBMS_REDEFINITION.ABORT_REDEF_TABLE (

   uname                   IN  VARCHAR2, --Schema name of the tables

   orig_table              IN  VARCHAR2, -- Name of the table to be redefined

   int_table               IN  VARCHAR2, -- Name of the interim table

   part_name               IN  VARCHAR2 := NULL);

part_name -- Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined. Can take a comma-delimited list of partition names to be redefined.

CAN_REDEF_TABLE Procedure

This procedure determines if a given table can be redefined online. This is the first step of the online redefinition process. If the table is not a candidate for online redefinition, an error message is raised.

Syntax

DBMS_REDEFINITION.CAN_REDEF_TABLE (

   uname         IN  VARCHAR2,

   tname        IN  VARCHAR2,

   options_flag  IN  PLS_INTEGER := 1,

   part_name     IN  VARCHAR2 := NULL);

options_flag  - - Indicates the type of redefinition method to use. If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys. If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.

COPY_TABLE_DEPENDENTS Procedure

This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects

This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition table)

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

   uname                    IN  VARCHAR2,

   orig_table               IN  VARCHAR2,

   int_table                IN  VARCHAR2,

   copy_indexes             IN  PLS_INTEGER := 1,

   copy_triggers            IN  BOOLEAN     := TRUE,

   copy_constraints         IN  BOOLEAN     := TRUE,

   copy_privileges          IN  BOOLEAN     := TRUE,

   ignore_errors            IN  BOOLEAN     := FALSE,

   num_errors               OUT PLS_INTEGER,

   copy_statistics          IN  BOOLEAN     := FALSE,

   copy_mvlog               IN  BOOLEAN     := FALSE);

The user must check the column num_errors before proceeding to ensure that no errors occurred during the cloning of the objects

FINISH_REDEF_TABLE Procedure

This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure.

Syntax

DBMS_REDEFINITION.FINISH_REDEF_TABLE (

   uname                   IN   VARCHAR2,

   orig_table              IN   VARCHAR2,

   int_table               IN   VARCHAR2,

   part_name               IN   VARCHAR2 := NULL,

   dml_lock_timeout        IN   PLS_INTEGER := NULL,

   continue_after_errors   IN   BOOLEAN := FALSE);

REDEF_TABLE Procedure

This procedure provides a single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.

Syntax

DBMS_REDEFINITION.REDEF_TABLE (

   uname                       IN  VARCHAR2,

   tname                       IN  VARCHAR2,    

   table_compression_type      IN  VARCHAR2 := NULL, 

   table_part_tablespace       IN  VARCHAR2 := NULL, 

   index_key_compression_type  IN  VARCHAR2 := NULL,

   index_tablespace            IN  VARCHAR2 := NULL,

   lob_compression_type        IN  VARCHAR2 := NULL,

   lob_tablespace              IN  VARCHAR2 := NULL,

   lob_store_as                IN  VARCHAR2 := NULL); 

Examples

BEGIN

   DBMS_REDEFINITION.REDEF_TABLE(

     uname                        => 'TABOWNER2',

     tname                        => 'EMP2',

     table_compression_type       => 'ROW STORE COMPRESS ADVANCED',

     table_part_tablespace        => 'NEWTBS',

     index_key_compression_type   => 'COMPRESS 1',

     index_tablespace             => 'NEWIDXTBS',

     lob_compression_type         => 'COMPRESS HIGH',

     lob_tablespace               => 'SLOBTBS',

     lob_store_as                 => 'SECUREFILE');

END;

START_REDEF_TABLE Procedure

Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be redefined) with the desired attributes of the post-redefinition table, and then call this procedure to initiate the redefinition.

Syntax

DBMS_REDEFINITION.START_REDEF_TABLE (

   uname                   IN  VARCHAR2,

   orig_table              IN  VARCHAR2,

   int_table               IN  VARCHAR2,

   col_mapping             IN  VARCHAR2 := NULL,

   options_flag            IN  BINARY_INTEGER := 1,

   orderby_cols            IN  VARCHAR2 := NULL,

   part_name               IN  VARCHAR2 := NULL,

   continue_after_errors   IN  BOOLEAN := FALSE

   copy_vpd_opt            IN  BINARY_INTEGER := CONS_VPD_NONE);

Examples

Start redefinition of three partitions (sal03q1,sal03q2,sal03q3) in table 'STEVE.salestable' using three interim tables of int_salestable1, int_salestable2 and int_salestable3, respectively. The operation will continue on sal03q3 even if it fails on sal03q1.

DBMS_REDEFINITION.START_REDEF_TABLE(

   uname                 => 'STEVE',

   orig_table            => 'salestable',

   int_table             => 'int_salestable1, int_salestable2, int_salestable3',

   col_mapping           => NULL,

   options_flag          => DBMS_REDEFINITION.CONS_USE_ROWID,

   part_name             => 'sal03q1,sal03q2,sal03q3',

   continue_after_errors => TRUE);

SYNC_INTERIM_TABLE Procedure

This procedure keeps the interim table synchronized with the original table.

Syntax

DBMS_REDEFINITION.SYNC_INTERIM_TABLE (

   uname                   IN  VARCHAR2,

   orig_table              IN  VARCHAR2,

   int_table               IN  VARCHAR2,

   part_name               IN  VARCHAR2 := NULL,

   continue_after_errors   IN  BOOLEAN := FALSE);

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.