Shulou(Shulou.com)06/01 Report--

11g detailed description of DBMS_REDEFINITION in the official document:

*

Using DBMS_REDEFINITION

Overview

Constants

Operational Notes

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.

To achieve online redefinition, use locally materialized views. These logs track changes to the primary table and are used by the materialized view during refresh synchronization.

Constants

The DBMS_REDEFINITION package uses the constants shown in Table 110-1, "DBMS_REDEFINITION Constants":

Table 110-1 DBMS_REDEFINITION Constants

Constant

Type

Value

Description

CONS_CONSTRAINT

PLS_INTEGER

three

Used to specify that dependent object type is a constraint

CONS_INDEX

PLS_INTEGER

two

Used to specify that dependent object type is an index

CONS_MVLOG

PLS_INTEGER

ten

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

CONS_ORIG_PARAMS

PLS_INTEGER

one

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

CONS_TRIGGER

PLS_INTEGER

four

Used to specify that dependent object type is a trigger

CONS_USE_PK

BINARY_INTEGER

one

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

two

Used to indicate that the redefinition should be done using rowids

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 andCAN_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_USE_PK and CONS_USE_ROWID are constants that are used as inputs to the "options_flag" parameter in START_REDEF_TABLE and CAN_REDEF_TABLE procedures. CONS_USE_ROWID is used to indicate that the redefinition should be done using rowid, while CONS_USE_PK means that the redefinition should be done using a primary key or pseudo primary key (which is the only key for all component columns with NOT NULL constraints).

CONS_INDEX, CONS_MVLOG,CONS_TRIGGER and CONS_CONSTRAINT are used to specify the type of the dependent object being (un) registered inREGISTER_DEPENDENT_OBJECT Procedure and UNREGISTER_DEPENDENT_OBJECT Procedure (parameter "dep_type")

CONS_INDEX,CONS_MVLOG,CONS_TRIGGER and CONS_CONSTRAINT are used to specify the types of dependent objects registered in the REGISTER_DEPENDENT_OBJECT procedure and the 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_MVLOG = = > dependent object is of type MATERIALIZED VIEW LOG

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.

CONS_ORIG_PARAMS as input to the "copy_indexes" parameter in the COPY_TABLE_DEPENDENTS procedure. Using this parameter means that the index on the original table is copied to the temporary table using the same storage parameters as the original index.

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

This procedure clears errors that occur during the redefinition process. This procedure can also be used to terminate the redefinition process at any time after the START_REDEF_TABLE program is called and before the FINISH_REDEF_TABLE procedure is called. This procedure deletes temporary objects 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 110-3 ABORT_REDEF_TABLE Procedure Parameters

Parameter

Description

Uname

Schema name of the tables

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

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

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

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 110-4 CAN_REDEF_TABLE Procedure Parameters

Parameter

Description

Uname

Schema name of the table

Tname

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

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

This procedure clones the table to a dependent object that is redefined on the temporary table and registers the dependent object. This process does not clone registered dependent objects.

This subroutine is used to clone dependent objects (such as authorizations, triggers, constraints, and permissions) from a redefined table to a temporary table (represent and then redefine the 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 110-5 COPY_TABLE_DEPENDENTS Procedure Parameters

Parameter

Description

Uname

Schema name of the tables

Orig_table

Name of the table being redefined

Int_table

Name of the interim table

Copy_indexes

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

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

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

In case of an error, the user should fix the cause of the error and call the COPY_TABLE_DEPENDENTS Procedure again to clone the dependent object. Alternatively the user can manually clone the dependent object and then register the manually cloned dependent object using theREGISTER_DEPENDENT_OBJECT Procedure.

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

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.

? The user must first check the column num_errors before continuing to ensure that no errors occur during the cloning of the object.

? If an error occurs, the user should fix the cause of the error and call the COPY_TABLE_DEPENDENTS procedure again to clone the dependent object. Alternatively, users can manually clone dependent objects and then register manually cloned dependent objects using the REGISTER_DEPENDENT_OBJECT process.

? All clone reference constraints involving temporary tables are disabled (automatically enabled when redefined), and all triggers on temporary tables are not triggered until the redefinition is complete. When the redefinition is complete, the cloned objects are renamed to the redefined names of the objects (from which they are cloned).

? Dependent objects that the user has the responsibility to clone are not affected by the redefinition. All triggers will be cloned, and the triggers that the user is responsible for cloning will not be affected 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.

This process completes the redefinition process. Prior to this step, you can create new indexes, triggers, grants, and constraints on temporary tables. Reference constraints involving temporary tables must be disabled. After completing this step, the original table is redefined as the properties and data of the temporary table. During this process, the original table is briefly locked.

Syntax

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

Parameters

Table 110-6 FINISH_REDEF_TABLE Procedure Parameters

Parameters

Description

Uname

Schema name of the tables

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

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.

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 process registers dependent objects (indexes, triggers, constraints, or materialized view logs) on the redefined table, and registers the corresponding dependent objects on the temporary 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

This can be used to have the same objects on each table, but with different properties. For example, for an index, the storage and tablespace properties may be different, but the columns of the index 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 110-7 REGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters

Description

Uname

Schema name of the tables

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

Dep_type

Type of the dependent object (see Constants and Operational Notes)

Dep_owner

Owner of the dependent object

Dep_orig_name

Name of the original dependent object

Dep_int_name

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.

? Trying to register a registered object raises an error.

? If there is an entry for the object, registering the dependent object automatically removes the object from the DBA_REDEFINITION_ERRORS.

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.

Before calling this procedure, you must manually create an empty temporary table (with the same schema as the table you want to redefine) with the required properties of the redefined table, and then call this procedure to start 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 110-8 START_REDEF_TABLE Procedure Parameters

Parameter

Description

Uname

Schema name of the tables

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

Col_mapping

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

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.

This process synchronizes the temporary table 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 110-9 SYNC_INTERIM_TABLE Procedure Parameters

Parameter

Description

Uname

Schema name of the table

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

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.

Usage Notes

This step is useful in minimizing the amount of synchronization needed to be done by the FINISH_REDEF_TABLE Procedure 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.

? This step is useful for minimizing the amount of synchronization that the FINISH_REDEF_TABLE process needs to complete before completing the online redefinition.

? You can call this procedure between long-running operations, such as CREATE INDEX, on a temporary table to synchronize it with the data in the original table and speed up subsequent operations.

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.

This process unregisters dependent objects (indexes, triggers, constraints, or materialized view logs) on the redefined table and unregisters the corresponding dependent objects on the temporary 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 110-10 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters

Description

Uname

Schema name of the tables

Orig_table

Name of the table to be redefined

Int_table

Name of the interim table

Dep_type

Type of the dependent object

Dep_owner

Owner of the dependent object

Dep_orig_name

Name of the original dependent object

Dep_int_name

Name of the interim dependent object

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.