Chapter 25. DBMS_REDEFINITION

Table of Contents

25.2.1. ABORT_REDEF_TABLE
25.2.2. CAN_REDEF_TABLE
25.2.3. COPY_TABLE_DEPENDENTS
25.2.4. FINISH_REDEF_TABLE
25.2.5. REGISTER_DEPENDENT_OBJECT
25.2.6. START_REDEF_TABLE
25.2.7. SYNC_INTERIM_TABLE
25.2.8. UNREGISTER_DEPENDENT_OBJECT

This chapter briefly introduces the DBMS_REDEFINITION package, and describes how to use the procedures of the package.

25.1. Overview

DBMS_REDEFINITION provides procedures for online redefinition of tables.

They are used to redefine a table as a new table with the specified column mapping information. The new table must be created before performing redefinition, and the structures of the original table and the new table are swapped after redefinition.

25.2. Procedures

This section describes the procedures provided by the DBMS_REDEFINITION package, in alphabetical order.

25.2.1. ABORT_REDEF_TABLE

Cancels the online table redefinition process. This procedure takes the original and newly defined table names and the column mapping information as arguments, and removes the internal objects created to redefine the table online and cancels the redefinition process. The original and new tables remain in the state of when the cancel was executed.

Details about the ABORT_REDEF_TABLE procedure are as follows:

Prototype

PROCEDURE ABORT_REDEF_TABLE 
(
    uname IN VARCHAR2, 
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2 
);

Parameter

Parameter Description
uname Name of the schema to which the original table and new table belong.
orig_table Original table name.
int_table New table name.

Example

BEGIN
				DBMS_REDEFINITION.ABORT_REDEF_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE');
END; /

25.2.2. CAN_REDEF_TABLE

Checks whether a table can be redefined online using the given table name and option. If the table is not online redefinable, an error occurs.

Details about the CAN_REDEF_TABLE procedure are as follows:

Prototype

PROCEDURE CAN_REDEF_TABLE 
(
    uname IN VARCHAR2, 
    tname IN VARCHAR12,
    options_flag IN BINARY_INTEGER := 1, 
    part_name IN VARCHAR2 := NULL
);

Parameter

Parameter	Description
uname	Table schema name.
tname	Name of the table to check if it is online redefinable.
options_flag	Value used to move data from the original table to the new table. cons_use_pk: use primary key. (Default value) cons_use_rowid: use ROWID.
part_name	Name of the partition to redefine. Must be specified to redefine a specific partition of the table. If set to NULL, the entire table is redefined.

Example

CREATE TABLE T1 (A NUMBER PRIMARY KEY);

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('TIBERO', 'T1');
END; /

25.2.3. COPY_TABLE_DEPENDENTS

Copies dependent objects of a table during online table redefinition. Dependent objects, such as indexes, triggers, constraints, and table privileges, can be copies to the new table by specifying them as parameters.

Details about the COPY_TABLE_DEPENDENTS procedure are as follows:

Prototype

PROCEDURE 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
);

Parameter

Parameter	Description
uname	Name of the schema to which original and new tables belong.
orig_table	Original table name.
int_table	New table name.
copy_indexes	Option to copy indexes. 0: do not copy indexes. dbms_redefinition.cons_orig_params: copy indexes.
copy_triggers	TRUE: copy triggers. FALSE: do not copy triggers.
copy_constraints	TRUE: copy constraints. FALSE: do not copy constraints.
copy_privileges	TRUE: copy table privileges. FALSE: do not copy table privileges.
ignore_errors	TRUE: copy dependent objects and ignore errors. FALSE: copy dependent objects and stop execution when an error occurs.
num_errors	Number of times an error occurred.
copy_statistics	Copy table statistics.
copy_mvlog	Copy table materialized view logs.

Example

CREATE TABLE T1 (A NUMBER PRIMARY KEY, B NUMBER);
CREATE INDEX I1 ON T1(B);
CREATE TABLE INT_T1 (A NUMBER, B NUMBER);

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'T1', 'INT_T1',
      'A A, B B'); 
END; 
/

DECLARE
num_errors pls_integer;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TIBERO', 'T1', 'INT_T1',
       DBMS_REDEFINITION.CONS_ORIG_PARAMS,
       TRUE, TRUE, TRUE, TRUE, num_errors, FALSE);
END;
/

25.2.4. FINISH_REDEF_TABLE

Completes the online table redefinition process. This procedure takes the original and newly defined table names as arguments, and exchanges data modified during the redefinition process and synchronizes the table names. This complete the redefinition process. Since this process requires changing of objects, it can only be performed when there are no transactions being executed on the table.

Details about the FINISH_REDEF_TABLE procedure are as follows:

Prototype

PROCEDURE FINISH_REDEF_TABLE 
( 
    uname IN VARCHAR2, 
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2 
);

Parameter

Parameter Description
uname Name of the schema to which the original and new tables belong.
orig_table Original table name.
int_table New table name.

Parameter	Description
uname	Name of the schema to which the original and new tables belong.
orig_table	Original table name.
int_table	New table name.

Example

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('TIBERO', 
 'ORIG_TABLE', 'INT_TABLE');
END; /

25.2.5. REGISTER_DEPENDENT_OBJECT

Maps dependent objects of the original table to those of the new table. Mapped object names are swapped during an online table redefinition.

Details about the REGISTER_DEPENDENT_OBJECT procedure are as follows:

Prototype

PROCEDURE 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 
);

Parameter

Parameter	Description
uname	Name of the schema to which original and new tables belong.
orig_table	Original table name.
int_table	New table name.
dep_type	Dependent object type.
dep_owner	Owner of the dependent object.
dep_orig_name	Dependent object name of the original table.
dep_int_name	Dependent object name of the new table.

Example

CREATE TABLE T1 (A NUMBER PRIMARY KEY, B NUMBER);
CREATE INDEX I1 ON T1(B);
CREATE TABLE INT_T1 (A NUMBER, B NUMBER);
CREATE INDEX INT_I1 ON INT_T1(B);

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'T1', 'INT_T1',
     'A A, B B'); 
END; 
/

BEGIN
   DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('TIBERO', 'T1', 'INT_T1',
   DBMS_REDEFINITION.CONS_INDEX,
  'TIBERO', 'I1', 'INT_I1');
END;
/

25.2.6. START_REDEF_TABLE

Starts the online table redefinition process. This procedure takes the original and newly defined table names and the column mapping information and copies data from the original table to the new table.

The new table should be created before executing this procedure, and it should exist in the same schema as that of the original table. Objects dependent on the table structure, such as partitions, indexes, constraints, and triggers, do not have to exist in the same location as those of the original table.

Note

Column mapping information maps the columns in the new table with columns in the original table.

Details about the START_REDEF_TABLE procedure are as follows:

Prototype

PROCEDURE START_REDEF_TABLE 
( 
			 uname IN VARCHAR2,
			 orig_table IN VARCHAR2,
			 int_table IN VARCHAR2,
			 col_mapping IN VARCHAR2 DEFAULT NULL, 
			 options_flag IN BINARY_INTEGER DEFAULT cons_use_pk 
);

Parameter

Parameter	Description
uname	Name of the schema to which the original table and new table belong.
orig_table	Original table name.
int_table	New table name.
col_mapping	Column mapping between the original table and the new table.
options_flag	Value used to move data from the original table to the new table. cons_use_pk: use primary key. (Default value) cons_use_rowid: use ROWID.

Example

create table ORIG_TABLE ( 
   PRODUCT_ID NUMBER primary key,
   PRODUCT_NAME VARCHAR2(20), 
   PRICE NUMBER, 
   SOLD_DATE DATE );

insert into ORIG_TABLE values (1, 'Tibero', 10000, SYSDATE);

commit;
create table INT_TABLE ( 
   ID NUMBER primary key, 
   NAME VARCHAR2(30), 
   PRICE NUMBER(8,2), 
   SOLD_DATE DATE )
partition by range (SOLD_DATE) 
( 
   partition P_2008 values less than ('2009-01-01'), 
   partition P_2009 values less than (maxvalue)
);
      
BEGIN 
   DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'ORIG_TABLE',
   'INT_TABLE', 'PRODUCT_ID ID, PRODUCT_NAME NAME, PRICE PRICE, 
    SOLD_DATE SOLD_DATE');
END; 
/

25.2.7. SYNC_INTERIM_TABLE

Synchronizes data in the original table and the newly defined table. The FINISH_REDEF_TABLE procedure can also synchronize data in the tables, but if there are too many DML statements to be performed in the original table during the redefinition process, using the FINISH_REDEF_TABLE procedure may reduce performance since it applied the modified data in one operation. Executing the DML statements may also be difficult because transaction processing is not allowed during the redefinition process.

The SYNC_INTERIM_TABLE procedure can synchronize data even during the redefinition process, and can improve the processing speed.

Details about the SYNC_INTERIM_TABLE procedure are as follows:

Prototype

PROCEDURE SYNC_INTERIM_TABLE 
( 
			 uname IN VARCHAR2, 
			 orig_table IN VARCHAR2,
			 int_table IN VARCHAR2 
);

Parameter

Parameter Description
uname Name of the schema to which the original table and new table belong.
orig_table Original table name.
int_table New table name.

Examples

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE');
END; /

25.2.8. UNREGISTER_DEPENDENT_OBJECT

Releases dependent object mapping between the original and new tables.

Details about the UNREGISTER_DEPENDENT_OBJECT procedure are as follows:

Prototype

PROCEDURE 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 
);

Parameter

Parameter	Description
uname	Name of the schema to which original and new tables belong.
orig_table	Original table name.
int_table	New table name.
dep_type	Dependent object type.
dep_owner	Owner of the dependent object.
dep_orig_name	Dependent object name of the original table.
dep_int_name	Dependent object name of the new table.

Example

CREATE TABLE T1 (A NUMBER PRIMARY KEY, B NUMBER);
CREATE INDEX I1 ON T1(B);
CREATE TABLE INT_T1 (A NUMBER, B NUMBER);
CREATE INDEX INT_I1 ON INT_T1(B);

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'T1', 'INT_T1',
     'A A, B B'); 
END; 
/

BEGIN
   DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('TIBERO', 'T1', 'INT_T1',
      DBMS_REDEFINITION.CONS_INDEX,
      'TIBERO', 'I1', 'INT_I1');
END;
/

BEGIN
   DBMS_REDEFINITION.UNREGISTER_DEPENDENT_OBJECT('TIBERO', 'T1', 'INT_T1',
   DBMS_REDEFINITION.CONS_INDEX,
     'TIBERO', 'I1', 'INT_I1');
END;
/

Prev		Next
Chapter 24. DBMS_RANDOM	Home	Chapter 26. DBMS_REPAIR