68
DBMS_REDEFINITION

The DBMS_REDEFINITION package provides an interface to perform an online redefinition of tables.

See Also:

Oracle Database Administrator's Guide for more information.

This chapter contains the following topics:

Using DBMS_REDEFINITION

Overview

To achieve online redefinition, incrementally maintainable local materialized views are used. Materialized view logs need to be defined on the master tables to support incrementally maintainable materialized views. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization.

Constants

The following constants are defined for this package:

cons_orig_params constant PLS_INTEGER := 1;
cons_use_pk constant BINARY_INTEGER := 1;
cons_use_rowid constant BINARY_INTEGER := 2;
cons_index constant PLS_INTEGER := 2;
cons_constraint constant PLS_INTEGER := 3;
cons_trigger constant PLS_INTEGER := 4;

Operational Notes

cons_use_pk and cons_use_rowid are constants used as input to the "options_flag" parameter in both the START_REDEF_TABLE Procedure and CAN_REDEF_TABLE Procedure. cons_use_rowid is used to indicate that the redefinition should be done using rowids while cons_use_pk implies that the redefinition should be done using primary keys or pseudo-primary keys (which are unique keys with all component columns having NOT NULL constraints).
cons_index, cons_trigger and cons_constraint are used to specify the type of the dependent object being (un)registered in REGISTER_DEPENDENT_OBJECT Procedure and UNREGISTER_DEPENDENT_OBJECT Procedure (parameter "dep_type").

cons_index ==> dependent object is of type INDEX

cons_trigger ==> dependent object is of type TRIGGER

cons_constraint==> dependent object type is of type CONSTRAINT
cons_orig_params as used as input to the "copy_indexes" parameter in COPY_TABLE_DEPENDENTS Procedure. Using this parameter implies that the indexes on the original table be copied onto the interim table using the same storage parameters as that of the original index.

Summary of DBMS_REDEFINITION Subprograms

Table 68-1 DBMS_REDEFINITION Package Subprograms

Subprogram	Description
ABORT_REDEF_TABLE Procedure	Cleans up errors that occur during the redefinition process.
CAN_REDEF_TABLE Procedure	Determines if a given table can be redefined online.
COPY_TABLE_DEPENDENTS Procedure	Copies the dependent objects of the original table onto the interim table.
FINISH_REDEF_TABLE Procedure	Completes the redefinition process.
REGISTER_DEPENDENT_OBJECT Procedure	Registers a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.
START_REDEF_TABLE Procedure	Initiates the redefinition process.
SYNC_INTERIM_TABLE Procedure	Keeps the interim table synchronized with the original table.
UNREGISTER_DEPENDENT_OBJECT Procedure	Unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.

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 start_redef_table has been called and before finish_redef_table is called.

Syntax

DBMS_REDEFINITION.ABORT_REDEF_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

Table 68-2 ABORT_REDEF_TABLE Procedure Parameters

Parameter	Description
`uname`	The schema name of the tables.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.

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

Parameters

Table 68-3 CAN_REDEF_TABLE Procedure Parameters

Parameter	Description
`uname`	The schema name of the table.
`tname`	The name of the table to be redefined.
`options_flag`	Indicates the type of redefinition method to use. If the value of this flag is `dbms_redefinition.cons_use_pk`, then the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having `NOT` `NULL` constraints). If the value of this flag is `dbms_redefinition.cons_use_rowid`, then the redefinition is done using rowids. The default method of redefinition is using primary keys.

Exceptions

If the table is not a candidate for online redefinition, an error message is raised.

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 procedure does not clone the already registered dependent objects.

This API will be used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined onto the interim table (which represents the post-redefinition table).

All cloned referential constraints involving the interim tables will be created disabled (they will be automatically enabled after the redefinition) and all triggers on interim tables will not fire till the redefinition is completed. After the redefinition is complete, the cloned objects will be renamed to the corresponding pre-redefinition names of the objects (from which they were cloned from).

Syntax

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname                    IN VARCHAR2,
   orig_table               IN VARCHAR2,
   int_table                IN VARCHAR2,
   copy_indexes             IN  PLS_INTEGER := 0,
   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);

Parameters

Table 68-4 COPY_TABLE_DEPENDENTS Procedure Parameters

Parameter	Description
uname	The schema name of the tables.
orig_table	The name of the table being redefined.
int_table	The name of the interim table.
`copy_indexes`	A flag indicating whether to copy the indexes 0 - don't copy any index `dbms_redefinition`.`cons_orig_params` - copy the indexes using the physical parameters of the source indexes
`copy_triggers`	`TRUE` implies clone triggers, `FALSE` implies do nothing
`copy_constraints`	`TRUE` implies clone constraints, `FALSE` implies do nothing
`copy_privileges`	`TRUE` implies clone privileges, `FALSE` implies do nothing
`ignore_errors`	`TRUE` implies if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. `FALSE` implies that the cloning process should stop upon encountering an error.
`num_errors`	The number of errors that occurred while cloning dependent objects

Usage Notes

It is the user's responsibility that the cloned dependent objects are unaffected by the redefinition. All the triggers will be cloned and it is the user's responsibility that the cloned triggers are unaffected by the redefinition.

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_REDFINITION.FINISH_REDEF_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

Table 68-5 FINISH_REDEF_TABLE Procedure Parameters

Parameters	Description
`uname`	The schema name of the tables.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.

REGISTER_DEPENDENT_OBJECT Procedure

This procedure registers a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.

Syntax

DBMS_REDFINITION.REGISTER_DEPEPENDENT_OBJECT(
   uame              IN VARCHAR2,
   orig_table        IN VARCHAR2,
   int_table         IN VARCHAR2,
   dep_type          IN  PLS_INTEGER,
   dep_owner         IN VARCHAR2,
   dep_orig_name     IN VARCHAR2,
   dep_int_name      IN  VARCHAR2);

Parameters

Table 68-6 REGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters	Description
`uname`	The schema name of the tables.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.
`dep_type`	The type of the dependent object.
`dep_owner`	The owner of the dependent object.
`dep_orig_name`	The name of the original dependent object.
`dep_int_name`	The name of the interim dependent object.

Usage Notes

Attempting to register an already registered object will raise an error.
Registering a dependent object will automatically remove that object from DBA_REDEFINITION_ERRORS if an entry exists for that object.

START_REDEF_TABLE Procedure

After verifying that the table can be redefined online, you 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_REDINITION.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 );

Parameters

Table 68-7 START_REDEF_TABLE Procedure Parameters

Parameter	Description
`uname`	The schema name of the tables.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.
`col_mapping`	The mapping information from the columns in the original table to the columns in the interim table. (This is similar to the column list on the `SELECT` clause of a query.) If `NULL,` all the columns in the original table are selected and have the same name after redefinition.
`options_flag`	Indicates the type of redefinition method to use. If the value of this flag is `dbms_redefinition.cons_use_pk`, then the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having `NOT` `NULL` constraints). If the value of this flag is `dbms_redefinition.cons_use_rowid`, then the redefinition is done using rowids. The default method of redefinition is using primary keys.
`orderby_cols`	This optional parameter accepts the list of columns (along with the optional keyword(s) ascending/descending) with which to order by the rows during the initial instantiation of the interim table (the order by is only done for the initial instantiation and not for subsequent synchronizations)

SYNC_INTERIM_TABLE Procedure

This procedure keeps the interim table synchronized with the original table. This step is useful in minimizing the amount of synchronization needed to be done by finish_redef_table before completing the online redefinition. This procedure can be called between long running operations (such as create index) on the interim table to sync it up with the data in the original table and speed up subsequent operations.

Syntax

DBMS_REDFINITION.SYNC_INTERIM_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN  VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

Table 68-8 SYNC_INTERIM_TABLE Procedure Parameters

Parameter	Description
`uname`	The schema name of the table.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.

UNREGISTER_DEPENDENT_OBJECT Procedure

This procedure unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.

Syntax

DBMS_REDFINITION.UNREGISTER_DEPEPENDENT_OBJECT(
   uname              IN VARCHAR2,
   orig_table        IN VARCHAR2,
   int_table         IN VARCHAR2,
   dep_type          IN PLS_INTEGER,
   dep_owner         IN VARCHAR2,
   dep_orig_name     IN VARCHAR2,
   dep_int_name      IN  VARCHAR2);

Parameters

Table 68-9 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters	Description
`uname`	The schema name of the tables.
`orig_table`	The name of the table to be redefined.
`int_table`	The name of the interim table.
`dep_type`	The type of the dependent object.
`dep_owner`	The owner of the dependent object.
`dep_orig_name`	The name of the original dependent object.
`dep_int_name`	The name of the interim dependent object.

68 DBMS_REDEFINITION

Using DBMS_REDEFINITION

Overview

Constants

Operational Notes

Summary of DBMS_REDEFINITION Subprograms

Table 68-1 DBMS_REDEFINITION Package Subprograms

ABORT_REDEF_TABLE Procedure

Syntax

Parameters

Table 68-2 ABORT_REDEF_TABLE Procedure Parameters

CAN_REDEF_TABLE Procedure

Syntax

Parameters

Table 68-3 CAN_REDEF_TABLE Procedure Parameters

Exceptions

COPY_TABLE_DEPENDENTS Procedure

Syntax

Parameters

Table 68-4 COPY_TABLE_DEPENDENTS Procedure Parameters

Usage Notes

FINISH_REDEF_TABLE Procedure

Syntax

Parameters

Table 68-5 FINISH_REDEF_TABLE Procedure Parameters

REGISTER_DEPENDENT_OBJECT Procedure

Syntax

Parameters

Table 68-6 REGISTER_DEPENDENT_OBJECT Procedure Parameters

Usage Notes

START_REDEF_TABLE Procedure

Syntax

Parameters

Table 68-7 START_REDEF_TABLE Procedure Parameters

SYNC_INTERIM_TABLE Procedure

Syntax

Parameters

Table 68-8 SYNC_INTERIM_TABLE Procedure Parameters

UNREGISTER_DEPENDENT_OBJECT Procedure

Syntax

Parameters

Table 68-9 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters

68
DBMS_REDEFINITION