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 tablesThis chapter contains the following topics:
Overview
Constants
Operational Notes
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.
The DBMS_REDEFINITION
package uses the constants shown in Table 77-1, "DBMS_REDEFINITION Constants":
Table 77-1 DBMS_REDEFINITION Constants
Constant | Type | Value | Description |
---|---|---|---|
|
|
|
Used to specify that dependent object type is a constraint |
|
|
|
Used to specify that dependent object type is a index |
|
|
|
Used to specify that indexes should be cloned with their original storage parameters |
|
|
|
Used to specify that dependent object type is a trigger |
|
|
|
Used to indicate that the redefinition should be done using primary keys or pseudo-primary keys (unique keys with all component columns having not- |
|
|
|
Used to indicate that the redefinition should be done using rowids |
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.
For information about various rules and limits that apply to implementation of this package, see the Oracle Database Administrator's Guide.
Table 77-2 DBMS_REDEFINITION Package Subprograms
Subprogram | Description |
---|---|
Cleans up errors that occur during the redefinition process and removes all temporary objects created by the reorganization process |
|
Determines if a given table can be redefined online |
|
Copies the dependent objects of the original table onto the interim table |
|
Completes the redefinition process. |
|
Registers a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table |
|
Initiates the redefinition process |
|
Keeps the interim table synchronized with the original table |
|
Unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table |
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.
DBMS_REDEFINITION.ABORT_REDEF_TABLE ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, part_name IN VARCHAR2 := NULL);
Table 77-3 ABORT_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
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.
DBMS_REDEFINITION.CAN_REDEF_TABLE ( uname IN VARCHAR2, tname IN VARCHAR2, options_flag IN PLS_INTEGER := 1, part_name IN VARCHAR2 := NULL);
Table 77-4 CAN_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
The schema name of the table |
|
The name of the table to be re-organized |
|
Indicates the type of redefinition method to use.
|
|
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
If the table is not a candidate for online redefinition, an error message is raised.
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).
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);
Table 77-5 COPY_TABLE_DEPENDENTS Procedure Parameters
Parameter | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table being redefined. |
|
The name of the interim table. |
|
A flag indicating whether to copy the indexes
|
|
|
|
|
|
|
|
|
|
The number of errors that occurred while cloning dependent objects |
|
|
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 the REGISTER_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.
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.
DBMS_REDEFINITION.FINISH_REDEF_TABLE ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, part_name IN VARCHAR2 := NULL);
Table 77-6 FINISH_REDEF_TABLE Procedure Parameters
Parameters | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
This procedure registers a dependent object (index, trigger or constraint) 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
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);
Table 77-7 REGISTER_DEPENDENT_OBJECT Procedure Parameters
Parameters | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
The type of the dependent object. |
|
The owner of the dependent object. |
|
The name of the original dependent object. |
|
The name of the interim dependent object. |
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.
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.
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);
Table 77-8 START_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
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 |
|
Indicates the type of redefinition method to use.
|
|
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) |
|
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
This procedure keeps the interim table synchronized with the original table.
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, part_name IN VARCHAR2 := NULL);
Table 77-9 SYNC_INTERIM_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
The schema name of the table. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
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 procedure unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.
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);
Table 77-10 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters
Parameters | Description |
---|---|
|
The schema name of the tables. |
|
The name of the table to be redefined. |
|
The name of the interim table. |
|
The type of the dependent object. |
|
The owner of the dependent object. |
|
The name of the original dependent object. |
|
The name of the interim dependent object. |