Summary of DBMS_REDEFINITION Subprograms

Table 50-1 DBMS_REDEFINITION Subprograms

Subprogram	Description
CAN_REDEF_TABLE Procedure	Determines if a given table can be redefined online.
START_REDEF_TABLE Procedure	Initiates the redefinition process.
FINISH_REDEF_TABLE Procedure	Completes the redefinition process.
SYNC_INTERIM_TABLE Procedure	Keeps the interim table synchronized with the original table.
ABORT_REDEF_TABLE Procedure	Cleans up errors that occur during the redefinition process.

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  BINARY_INTEGER := 1);

Exceptions

If the table is not a candidate for online redefinition, an error message is raised.

Parameters

Table 50-2 CAN_REDEF_TABLE Procedure Parameters

Parameter	Description
uname	The schema name of the table.
tname	The name of the table to be redefined.
options_flag	Indicates the type of redefinition method to use. If the value of this flag is dbms_redefinition.cons_use_pk, then the redefinition is done using primary keys. If the value of this flag is dbms_redefinition.cons_use_rowid, then the redefinition is done using rowids. The default method of redefinition is using primary keys.

START_REDEF_TABLE Procedure

This procedure initiates the redefinition process. After verifying that the table can be redefined online, you create an empty interim table (in the same schema as the table to be redefined) with the desired attributes of the post-redefinition table.

Syntax

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

Parameters

Table 50-3 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 interim table to the columns in the original 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 the value of this flag is dbms_redefinition.cons_use_pk, then the redefinition is done using primary keys. If the value of this flag is dbms_redefinition.cons_use_rowid, then the redefinition is done using rowids. The default method of redefinition is using primary keys.

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_REDFINITION.finish_redef_table (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

Table 50-4 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.

SYNC_INTERIM_TABLE Procedure

This procedure keeps the interim table synchronized with the original table. This step is useful in minimizing the amount of synchronization needed to be done by finish_reorg_table 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.

Syntax

DBMS_REDFINITION.sync_interim_table (
   uname       IN VARCHAR2,
   orig_table  IN  VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

Table 50-5 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.

ABORT_REDEF_TABLE Procedure

This procedure cleans up errors that occur during the redefinition process. This procedure can also be used to abort the redefinition process any time after start_reorg_table has been called and before finish_reorg_table is called.

Syntax

DBMS_REDEFINITION.abort_redef_table (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2);

Parameters

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