-- Check table can be redefined

EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMPLOYEES');

 

-- Create new table with CTAS

CREATE TABLE scott.employees2

TABLESPACE tools AS

SELECT empno, first_name, salary as sal

FROM employees WHERE 1=2;

 

-- Start Redefinition

EXEC Dbms_Redefinition.Start_Redef_Table( -

  'SCOTT', -

  'EMPLOYEES', -

  'EMPLOYEES2', -

  'EMPNO EMPNO, FIRST_NAME FIRST_NAME, SALARY*1.10 SAL);

 

-- Optionally synchronize new table with interim data

EXEC dbms_redefinition.sync_interim_table( -

  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');

 

-- Add new keys, FKs and triggers

ALTER TABLE employees2 ADD

(CONSTRAINT emp_pk2 PRIMARY KEY (empno)

USING INDEX

TABLESPACE indx);

 

-- Complete redefinition

EXEC Dbms_Redefinition.Finish_Redef_Table( -

  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');

 

-- Remove original table which now has the name of the new table

DROP TABLE employees2;

 

Online Redefinition

In a very active database environment, it sometimes becomes necessary to modify a hot table to improve the performance of queries and DML statements. To achieve this, Oracle provides a mechanism called online redefinition.

When a table is redefined online, it is accessible to DML during much of the redefinition process. However, the table is locked in the exclusive mode only during a very small period. This window or period is usually independent of the size of the table and the complexity of the redefinition.

The Online Redefinition process was made available in Oracle9i. However, with Oracle Database 10g, this feature becomes much more easy to use and thus useful for redefining online tables. Oracle Database 10g introduces many useful enhancements, thereby increasing support for 24/7 database availability.

New Additions and Changes to dbms_redefinition

In Oracle, there were many restrictions to performing redefinition. It was even necessary to create dependent objects, such as constraints and indexes, manually. Oracle Database 10g introduces many enhancements to facilitate easier administration and to minimize manual tasks. They are discussed next.

Support for additional data types, such as CLOB and BLOB has been introduced. Tables containing character LOBS and binary LOBS can now be redefined online. However, tables with BFILE columns cannot be redefined online.

Tables that are involved in master-master replication can undergo redefinition.  However, there is a restriction that no horizontal or vertical sub-setting, or column transformations, are allowed.

Three new procedures are added to the package dbms_redefinition . They are as follows:

-  copy_table_dependents   - clones the dependent objects of the table being redefined.
-  register_dependent_object - registers a dependent object on the table being redefined and on the interim table
-  unregister_dependent_object  - un-registers a dependent object on the table being redefined and on the interim table.

The following constants are also introduced in specifying the type of the dependent object.

-  cons_constraint   - specifies the dependent object type CONSTRAINT.
-  cons_index  - specifies the dependent object type is of type Index.
-  cons_trigger  - specifies the dependent object type is of TRIGGER
-  copy_orig_params  - specifies that the indexes on the original table be copied using the storage parameters of the original.

A new optional parameter, orderby_cols , has been introduced for the start_redef_table  procedure. You can use this parameter to specify the columns that should be used to order the rows during the initial instantiation of the interim table. In addition, you can also use the optional key word ASCENDING or DESCENDING.

The remaining restrictions are shown below:

-  The table to be redefined cannot be part of a cluster.

-  Tables in the SYS and SYSTEM schemas cannot be online redefined.

-  Temporary tables cannot be redefined.

-  Tables with BFILE columns cannot be online redefined.

- The overflow table of an index-organized table cannot be online redefined.

-  Tables with user-defined types (objects, REFs, collections, typed tables) cannot be redefined online.

-  Table redefinition cannot be done NOLOGGING.

 

Example of online redefinition of a table

Let us look at the total online redefinition process using these newly introduced features and enhancements. We have a table called dly_price with the structure shown below and it has a primary key on ticker and date_px. It also has two additional indexes.

SQL>desc  dly_price

 Name                     Null?    Type

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

 TICKER                          VARCHAR2(4)

 CLOSE_PX                        NUMBER

 OPEN_PX                         NUMBER

 LOW_PX                          NUMBER

 DATE_PX                         DATE

It also has the following indexes.

Table_Name      Index_Name     Pos Column_Name

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

* DLY_PRICE    DLY_PRICE_IND1    1   TICKER

* DLY_PRICE    DLY_PRICE_IND2    1   DATE_PX

* DLY_PRICE    SYS_C003340       1   TICKER

* DLY_PRICE    SYS_C003340       2   DATE_PX

We intend to redefine the table by dropping one column, modifying another column, and adding an additional column.

Step 1.

First, determine the method of redefinition. Here, we will use the primary key to perform the redefinition. When we set the value of dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys. If you set the value to dbms_redefinition.cons_use_rowid, then the redefinition is done using ROWIDs. This procedure will also determine whether the table is redefinable according to the documented limitations, and if not, issue an error message telling why the table is not redefinable.

SQL>exec DBMS_REDEFINITION.CAN_REDEF_TABLE('test1', -'dly_price', dbms_redefinition.cons_use_pk);

 

PL/SQL procedure successfully completed.

 

Step 2.

We need to create the interim table with an appropriate definition. This table structure will reflect addition, modification, and deletion of columns.

 

SQL>create table int_dly_price

  2  ( ticker varchar2(6),

  3    close_px number,

  4    low_px number,

  5    high_px number,

  6    date_px date) ;

 

Table created.

As we see in the create table statement, we have added a high_px column and removed the open_px column. Also notice that we have increased ticker column size to 6 from 4.

Step 3.

Now start the online redefinition process by executing the start_redef_table procedure. Supply the parameters, such as table to be redefined, interim table name, the column mapping, and the method of redefinition. Optionally, we can also specify the columns to be used for ordering rows by using the orderby_cols . This is a new feature in Oracle Database 10g.

 

SQL>BEGIN

  2  DBMS_REDEFINITION.START_REDEF_TABLE('test1', 'dly_price','int_dly_price',

  3  'ticker ticker, close_px close_px, low_px low_px, 0 high_px, date_px date_px',

  4  dbms_redefinition.cons_use_pk);

  5  END ;

/

 

PL/SQL procedure successfully completed.

 

As you will notice, high_px is a new column and will have the value of '0'. And open_px does not appear in the column mapping, since it gets removed after redefinition.

Step 4.

In this step, we will use the copy_table_dependents  procedure to automatically copy all dependent objects, such as triggers, indexes, and privileges to the interim table. This procedure is newly introduced in Oracle Database 10g to ease the redefinition process.

 

SQL>Declare

  2  blk_out pls_integer ;

  3  BEGIN

  4  blk_out := 0 ;

  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('test1', 'dly_price','int_dly_price',1,

  6  TRUE, TRUE, TRUE, FALSE, blk_out);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

Step 5.

Now we finish the process with the finish_redef_table  procedure.

SYS>exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('test1', 'dly_price','int_dly_price') ;

 

PL/SQL procedure successfully completed.

Now, the redefined table shows the structure below:

 

SYS>desc dly_price ;

 Name                     Null?    Type

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

 TICKER                          VARCHAR2(6)

 CLOSE_PX                        NUMBER

 LOW_PX                          NUMBER

 HIGH_PX                         NUMBER

 DATE_PX                         DATE

Step 6.

Then we drop the interim table after ensuring the desired results are achieved.

You can also query dba_redefinition_errors to determine whether any errors occurred during the cloning of dependent objects with the copy_table_dependents  procedure. This view shows the errors that occurred while cloning dependent objects involved in ongoing redefinitions.