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