Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

109 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 about online redefinition of tables

This chapter contains the following topics:


Using DBMS_REDEFINITION


Overview

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


Constants

The DBMS_REDEFINITION package uses the constantsshown in Table 109-1, "DBMS_REDEFINITION Constants":

Table 109-1 DBMS_REDEFINITION Constants

Constant Type Value Description

CONS_CONSTRAINT

PLS_INTEGER

3

Used to specify that dependent object type is a constraint

CONS_INDEX

PLS_INTEGER

2

Used to specify that dependent object type is a index

CONS_MVLOG

PLS_INTEGER

10

Used to (un)register a materialized view log, as a dependent object of the table, through the REGISTER_DEPENDENT_OBJECT Procedureand the UNREGISTER_DEPENDENT_OBJECT Procedure.

CONS_ORIG_PARAMS

PLS_INTEGER

1

Used to specify that indexes should be cloned with their original storage parameters

CONS_TRIGGER

PLS_INTEGER

4

Used to specify that dependent object type is a trigger

CONS_USE_PK

BINARY_INTEGER

1

Used to indicate that the redefinition should be done using primary keys or pseudo-primary keys (unique keys with all component columns having not-NULL constraints)

CONS_USE_ROWID

BINARY_INTEGER

2

Used to indicate that the redefinition should be done using rowids



Operational Notes


Rules and Limits

For information about various rules and limits that apply to implementation of this package, see the Oracle Database Administrator's Guide.


Summary of DBMS_REDEFINITION Subprograms

Table 109-2 DBMS_REDEFINITION Package Subprograms

Subprogram Description

ABORT_REDEF_TABLE Procedure

Cleans up errors that occur during the redefinition process and removes all temporary objects created by the reorganization 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, constraint or materialized view log) 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, constraint or materialized view log) 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 the START_REDEF_TABLE Procedure has been called and before the FINISH_REDEF_TABLE Procedure is called. This process will remove the temporary objects that are created by the redefinition process such as materialized view logs.

Syntax

DBMS_REDEFINITION.ABORT_REDEF_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2,
  part_name    IN  VARCHAR2 := NULL);

Parameters

Table 109-3 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.

part_name

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

Parameters

Table 109-4 CAN_REDEF_TABLE Procedure Parameters

Parameter Description

uname

The schema name of the table

tname

The name of the table to be re-organized

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.

part_name

The 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.


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

Syntax

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

Parameters

Table 109-5 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 - do not copy any index

  • dbms_redefinition.cons_orig_params – copy the indexes using the physical parameters of the source indexes

copy_triggers

TRUE = clone triggers, FALSE = do nothing

copy_constraints

TRUE = clone constraints, FALSE = do nothing. If compatibility setting is 10.2 or higher, then clone CHECK and NOT NULL constraints

copy_privileges

TRUE = clone privileges, FALSE = do nothing

ignore_errors

TRUE = if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE = that the cloning process should stop upon encountering an error.

num_errors

The number of errors that occurred while cloning dependent objects

copy_statistics

TRUE = copy statistics, FALSE = do nothing

copy_mvlog

TRUE = copy materialized view log, FALSE = do nothing


Usage Notes


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

Parameters

Table 109-6 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.

part_name

The 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.



REGISTER_DEPENDENT_OBJECT Procedure

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

This can be used to have the same object on each table but with different attributes. For example: for an index, the storage and tablespace attributes could be different but the columns indexed remain the same

Syntax

DBMS_REDEFINITION.REGISTER_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 109-7 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


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

Parameters

Table 109-8 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 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.

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)

part_name

The 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.



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

Parameters

Table 109-9 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.

part_name

The 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.


Usage Notes


UNREGISTER_DEPENDENT_OBJECT Procedure

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

Syntax

DBMS_REDEFINITION.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 109-10 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.