Examples Oracle Online Table Redefinition

Online Table Redefinition Examples

For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.

Example Description
Example 1 Redefines a table by adding new columns and adding partitioning.
Example 2 Demonstrates redefinition with object datatypes.
Example 3 Demonstrates redefinition with manually registered dependent objects.
Example 4 Redefines a single table partition, moving it to a different tablespace.


Example 1

This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:

•New columns mgr, hiredate, sal, and bonus are added. (These existed in the original table but were dropped in previous examples.)

•The new column bonus is initialized to 0

•The column deptno has its value increased by 10.

•The redefined table is partitioned by range on empno.

The steps in this redefinition are illustrated below.

1.Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2.Create an interim table hr.int_admin_emp.

CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);

3.Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/

4.Copy dependent objects. (Automatically create any triggers, indexes, grants, and constraints on hr.int_admin_emp.)

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

5.Query the DBA_REDEFINITION_ERRORS view to check for errors.

SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;

OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX "HR"."TMP$
$_SYS_C0058360" ON "HR"."INT_A
DMIN_EMP" ("EMPNO")

SYS_C005836 ADMIN_EMP ALTER TABLE "HR"."INT_ADMIN_EM
P" ADD CONSTRAINT "TMP$$_SYS_C
0058360" PRIMARY KEY

These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.

Note:

The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.
6.Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

7.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.

8.Drop the interim table.

Example 2

This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.

The original table, named CUSTOMER, is defined as follows:

Name Type
------------ -------------
CID NUMBER <- Primary key
NAME VARCHAR2(30)
STREET VARCHAR2(100)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER(5)

The type definition for the new object is:

CREATE TYPE ADDR_T AS OBJECT (
street VARCHAR2(100),
city VARCHAR2(30),
state VARCHAR2(2),
zip NUMBER(5, 0) );

Here are the steps for this redefinition:

1.Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('STEVE','CUSTOMER',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2.Create the interim table int_customer.

CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADDR_T);

Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.

3.Because CUSTOMER is a very large table, specify parallel operations for the next step.

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

4.Start the redefinition process using primary keys.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,
addr_t(street, city, state, zip) addr');
END;
/

Note that addr_t(street, city, state, zip) is a call to the object constructor.

5.Copy dependent objects.

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
'STEVE','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/

Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.

6.Optionally synchronize the interim table.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/

7.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/

8.Drop the interim table.

Example 3

This example addresses the situation where a dependent object must be manually created and registered.

Consider the case where a table T1 has a column named C1, and where this column becomes C2 after the redefinition. Assume that there is an index Index1 on C1. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to Index1, and tries to create it on a column C1, which does not exist on the interim table. This results in an error. You must therefore manually create the index on column C2 and register it. Here are the steps:

1.Create the interim table INT_T1 and create an index Int_Index1 on column C2.

2.Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.

3.Register the original (Index1) and interim (Int_Index1) dependent objects.

BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'STEVE',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'STEVE',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
/

4.Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.

5.Optionally synchronize the interim table.

6.Complete the redefinition and drop the interim table.

Example 4

This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. The table containing the partition to be redefined is defined as follows:

CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

The table has a local partitioned index that is defined as follows:

CREATE INDEX sales_index ON salestable
(s_saledate, s_productid, s_custid) LOCAL;

Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).

1.Ensure that salestable is a candidate for redefinition.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/

2.Create the interim table in the TBS_LOW_FREQ tablespace. Because this is a redefinition of a range partition, the interim table is non-partitioned.

CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;

3.Start the redefinition process using rowid.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/

4.Manually create any local indexes on the interim table.

CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;

5.Optionally synchronize the interim table.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/

6.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/

7.Drop the interim table.

The following query shows that the oldest partition has been moved to the new tablespace:

select partition_name, tablespace_name from user_tab_partitions
where table_name = 'SALESTABLE';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 TBS_LOW_FREQ
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS

4 rows selected.
Privileges Required for the DBMS_REDEFINITION Package
Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

•CREATE ANY TABLE

•ALTER ANY TABLE

•DROP ANY TABLE

•LOCK ANY TABLE

•SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

•CREATE ANY TRIGGER

•CREATE ANY INDEX


77 DBMS_REDEFINITIONThe 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

◦Constants

◦Operational Notes

•Summary of DBMS_REDEFINITION Subprograms


--------------------------------------------------------------------------------

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.


--------------------------------------------------------------------------------

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


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

Table 77-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



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.


--------------------------------------------------------------------------------

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

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

•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
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 77-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 77-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

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


--------------------------------------------------------------------------------

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