137 DBMS_REDEFINITION
The DBMS_REDEFINITION
package provides an interface to perform an online redefinition of tables.
This chapter contains the following topics:
See Also:
Oracle Database Administrator's Guide for more information about online redefinition of tables
137.1 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.
137.2 DBMS_REDEFINITION Security Model
Subprograms in the DBMS_REDEFINITION
package are run with invokers' rights (with the privileges of the current user).
There are two modes:
-
In
USER
mode, the user who has theCREATE TABLE
andCREATE MVIEW
privileges may redefine a table residing in his own schema. -
In
FULL
mode, the user who has theANY
privilege may redefine tables in any schema.
137.3 DBMS_REDEFINITION Constants
The DBMS_REDEFINITION
package defines several constants for specifying parameter values.
Table 137-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 (un)register a materialized view log, as a dependent object of the table, through the REGISTER_DEPENDENT_OBJECT Procedureand the UNREGISTER_DEPENDENT_OBJECT Procedure. |
|
|
|
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 |
|
|
|
Used to indicate to copy VPD policies automatically |
|
|
|
Used to indicate to copy VPD policies manually |
|
|
1 |
Used to indicate that there are no VPD policies on the original table |
137.4 DBMS_REDEFINITION Operational Notes
The following operational notes apply to DBMS_REDEFINITION.
-
CONS_USE_PK
andCONS_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 whileCONS_USE_PK
implies that the redefinition should be done using primary keys or pseudo-primary keys (which are unique keys with all component columns havingNOT
NULL
constraints). -
CONS_INDEX
,CONS_MVLOG
,CONS_TRIGGER
andCONS_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 typeINDEX
CONS_TRIGGER
==> dependent object is of typeTRIGGER
CONS_CONSTRAINT
==> dependent object type is of typeCONSTRAINT
CONS_MVLOG
==> dependent object is of typeMATERIALIZED
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.
137.5 DBMS_REDEFINITION Rules and Limits
Various rules and limits apply to implementation of the DBMS_REDEFINITION
package.
For more information about these, see the Oracle Database Administrator's Guide.
137.6 DBMS_REDEFINITION Examples
The following examples demonstrate DBMS_REDEFINITION
functionality.
We create two tables EMP
and EMP_INT
as the original and the interim tables, respectively:
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0))
TABLESPACE myts;
CREATE TABLE "EMP_INT"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0))
TABLESPACE compressed_ts;
Regular Multi-Step Redefinition
DBMS_REDEFINITION.START_REDEF_TABLE(‘SCOTT’, ‘EMP’, ‘EMP_INT’, ENABLE_ROLLBACK => TRUE);
DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘SCOTT’, ‘EMP’, ‘EMP_INT’);
Assume theDBA wants to evaluate the performance of the application for 2 days, after moving the table EMP
from tablespace myts
to compressed_ts
. One can run sync_interim_table SYNC_INTERIM_TABLE Procedureto keep both the tables in sync (say, every hour).
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘SCOTT’, ‘EMP’, ‘EMP_INT’);
Case 1 — DBA is not happy with the performance, so decides to rollback.
DBMS_REDEFINITION.ROLLBACK(‘SCOTT’, ‘EMP’, ‘EMP_INT’);
Case 2 — DBA is happy with the performance, so decides not to rollback.
DBMS_REDEFINITION.ABORT_ROLLBACK(‘SCOTT’, ‘EMP’, ‘EMP_INT’);
This terminates the possibility of rollback.
Single-Step Redefinition
DBMS_REDEFINITION.REDEF_TABLE(’SCOTT’,’EMP’,'ROW STORE COMPRESS ADVANCED', enable_rollback => TRUE);
Note:
Online table redefinition rollback is not supported when theREDEF_TABLE
procedure is used to redefine a table.
137.7 Summary of DBMS_REDEFINITION Subprograms
This table lists the DBMS_REDEFINITION
subprograms and briefly describes them.
Table 137-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 |
|
Aborts rollback |
|
Aborts an update started with the |
|
Determines if a given table can be redefined online |
|
Copies the dependent objects of the original table onto the interim table |
|
Optimizes the performance of bulk updates to a table |
|
Completes the redefinition process |
|
Provides a single push-button interface that integrates several redefinition steps |
|
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 |
|
Performs rollback |
|
Sets a new value for a specified parameter used by the redefinition process identified by a redefinition ID |
|
Initiates the redefinition process |
|
Keeps the interim table synchronized with the original table |
|
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 |
137.7.1 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 137-3 ABORT_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table. Can take a comma-delimited list of interim table names. |
|
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
137.7.2 ABORT_ROLLBACK Procedure
This procedure aborts rollback for a table that was redefined.
When online redefinition of a table is started with the START_REDEF_TABLE
procedure, rollback can be enabled for the changes performed by online redefinition of a table by setting the enable_rollback
parameter to TRUE
. If you want to retain the changes made by online redefinition, you can abort the rollback to clean up the database objects that enable rollback.
Syntax
DBMS_REDEFINITION.ABORT_ROLLBACK ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2 := NULL, part_name IN VARCHAR2 := NULL);
Parameters
Table 137-4 ABORT_ROLLBACK Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table |
|
Name of the partition being redefined |
137.7.3 ABORT_UPDATE Procedure
This procedure can aborts an update started with the EXECUTE_UPDATE
procedure in the RDBMS_REDEFINITION
package.
Syntax
DBMS_REDEFINITION.ABORT_UPDATE ( update_stmt IN CLOB);
Parameters
Table 137-5 ABORT_UPDATE Procedure Parameters
Parameter | Description |
---|---|
|
The SQL The SQL statement must exactly match the SQL statement in the |
See Also:
137.7.4 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 137-6 CAN_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the table |
|
Name of the table to be re-organized |
|
Indicates the type of redefinition method to use.
|
|
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
Exceptions
If the table is not a candidate for online redefinition, an error message is raised.
137.7.5 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 137-7 COPY_TABLE_DEPENDENTS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the tables |
|
Name of the table being redefined |
|
Name of the interim table |
|
Flag indicating whether to copy the indexes
|
|
|
|
|
|
|
|
|
|
Number of errors that occurred while cloning dependent objects |
|
|
|
|
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 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.
137.7.6 EXECUTE_UPDATE Procedure
This procedure can optimize the performance of bulk updates to a table. Performance is optimized because the updates are not logged in the redo log.
The EXECUTE_UPDATE
procedure automatically uses the components of online table redefinition, such an interim table, a materialized view, and a materialized view log, to enable optimized bulk updates to a table. The EXECUTE_UPDATE
procedure also removes fragmentation of the affected rows and ensures that the update is atomic. If the bulk updates raise any errors, then you can use the ABORT_UPDATE
procedure to undo the changes made by the EXECUTE_UPDATE
procedure.
Syntax
DBMS_REDEFINITION.EXECUTE_UPDATE ( update_stmt IN CLOB);
Parameters
Table 137-8 EXECUTE_UPDATE Procedure Parameters
Parameter | Description |
---|---|
|
The SQL |
See Also:
137.7.7 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, disable_rollback IN PLS_INTEGER := FALSE);
Parameters
Table 137-9 FINISH_REDEF_TABLE Procedure Parameters
Parameters | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table. Can take a comma-delimited list of interim table names. |
|
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
|
Specifies the number of seconds the procedure waits for its required locks before failing. The permissible range of values for timeout is 0 to 1,000,000. The default is |
|
When redefining multiple partitions allows operation execution to continue on the next partition (applies only to batched partition redefinition). |
|
When set to |
Examples
Wait up to 600 seconds for required locks on SH.SALES
:
EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE ( 'SH', 'SALES', 'INT_SALES', 600);
137.7.8 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, refresh_dep_mviews IN VARCHAR2 := 'N', dml_lock_timeout IN PLS_INTEGER := NULL);
Parameters
Table 137-10 REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the table |
|
Name of the table to be redefined |
|
Text string of the table compression clause. |
|
Tablespace name for the entire table or partitions. |
|
Text string of the compression clause for all indexes on the table. |
|
Tablespace name for all indexes on the table. |
|
Text string of the compression clause for all LOBs in the entire table. |
|
Tablespace name for all LOBs in the table. |
|
Specifies LOB store as |
|
When set to |
|
Specifies the number of seconds the procedure waits for its required locks before failing. The permissible range of values for timeout is 0 to 1,000,000. The default is |
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;
Related Topics
See Also:
Oracle Database Administrator's Guide regarding "Performing Online Redefinition with the REDEF_TABLE
Procedure"
137.7.9 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_DEPENDENT_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 137-11 REGISTER_DEPENDENT_OBJECT Procedure Parameters
Parameters | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table |
|
Type of the dependent object (see Constants and Operational Notes) |
|
Owner of the dependent object |
|
Name of the original dependent object |
|
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.
137.7.10 ROLLBACK Procedure
This procedure rolls back changes to a table after online table redefinition to return the table to its original definition and preserve DML changes made to the table.
Syntax
DBMS_REDEFINITION.ROLLBACK ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2 := NULL, part_name IN VARCHAR2 := NULL, dml_lock_timeout IN PLS_INTEGER := NULL, continue_after_errors IN BOOLEAN := FALSE);
Parameters
Table 137-12 ROLLBACK Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the table to be redefined |
|
Name of the table to be redefined |
|
Name of the interim table. |
|
Name of the partition being redefined. |
|
Specifies the number of seconds the procedure waits for its required locks before failing. The permissible range of values for timeout is 0 to 1,000,000. The default is |
|
When rolling back redefinition changes on multiple partitions, allows operation execution to continue on the next partition (applies only to batched partition redefinition). |
137.7.11 SET_PARAM Procedure
This procedure sets a new value for a specified parameter used by the redefinition process identified by a redefinition ID.
Note:
Currently, the only value that can be changed by this procedure is the value for the of the refresh_dep_mviews
parameter that is specified in the REDEF_TABLE
procedure or the START_REDEF_TABLE
procedure. You can determine the redefinition ID and check the value of the refresh_dep_mviews
parameter for an online table redefinition operation by querying the DBA_REDEFINITION_STATUS
view.
Syntax
DBMS_REDEFINITION.SET_PARAM ( redefinition_id IN VARCHAR2, param_name IN VARCHAR2, param_value IN VARCHAR2);
Parameters
Table 137-13 SET_PARAM Procedure Parameters
Parameter | Description |
---|---|
|
The redefinition ID that identifies the redefinition process |
|
The parameter name |
|
The new parameter value |
See Also:
137.7.12 START_REDEF_TABLE Procedure
This procedure starts a table redefinition.
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, refresh_dep_mviews IN VARCHAR2 := 'N', enable_rollback IN BOOLEAN := FALSE);
Parameters
Table 137-14 START_REDEF_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table. Can take a comma-delimited list of interim table names. |
|
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) |
|
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
|
When redefining multiple partitions allows operation execution to continue on the next partition (applies only to batched partition redefinition) |
|
Specifies how VPD policies are handled in online redefinition |
|
When set to |
|
When set to When this parameter is set to true, Oracle Database maintains the interim table created during redefinition after redefinition is complete. You can run the |
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);
Specify to copy VPD policies automatically:
EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE ( uname => 'SCOTT', orig_table => 'T', int_table => 'INT_T', copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO);
137.7.13 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);
Parameters
Table 137-15 SYNC_INTERIM_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the table |
|
Name of the table to be redefined |
|
Name of the interim table. Can take a comma-delimited list of interim table names. |
|
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. |
|
When redefining multiple partitions allows operation execution to continue on the next partition (applies only to batched partition redefinition) |
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.
137.7.14 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_DEPENDENT_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 137-16 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters
Parameters | Description |
---|---|
|
Schema name of the tables |
|
Name of the table to be redefined |
|
Name of the interim table |
|
Type of the dependent object |
|
Owner of the dependent object |
|
Name of the original dependent object |
|
Name of the interim dependent object |