Using Oracle dbms_redefinition

To solve the problem of doing table reorgs while the database accepts updates, Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.

The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the
table, enqueue changes during the redefinition, and then re-synchronize the restructured table
with the changes that have accumulated during reorganization.

exec dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');

alter table titles add constraint pk_titles primary key (title_id);

exec dbms_redefinition.can_redef_table('PUBS','TITLES');

create table titles2

as

select * from titles;

exec dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id title_id,title
title,type type,pub_id pub_id,price price,advance advance,royalty*1.1 royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');

exec dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');

exec dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');

If your reorganization fails, you must take special steps to make it re-start. Because the
redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to
release the snapshot to re-start you procedure.

The 'dbms_redefinition.abort_redef_table' procedure which accepts 3 parameters (schema, original table name, holding table name), and which "pops the stack" and allows you to start over.

No database is 100% self-reliant or self-maintaining, which is a good thing for DBA job security. However, the last few major versions of Oracle have greatly increased its self-diagnostic and self-monitoring capabilities. Only database structural reorganization remains one of those tasks best left to the DBA to decide when it is appropriate to perform and when to schedule its execution. That is because data is the life blood of any modern organization, and while doing various database reorganizations, the following possibilities exist:

• The process could blow-up mid-stream, so data may be left offline

• The process is resource-intensive and takes significant time to execute

• Data could be momentarily inconsistent between key steps

• Probably advisable to consider doing a backup operation just prior to

The key point is that structural reorganizations are generally important events in any database's life cycle. Even when a reorganization activity can theoretically be performed entirely online with little or no downtime, it is often a safer bet to perform any such activities in a controlled environment. Because the one time something that can not go wrong does, the DBA will be in a better situation to resume or recover if there are not frantic customers breathing down his neck. So schedule any reorganization event with extreme caution and over- compensation.

Now  with all that said, Oracle provides a robust and reliable package for performing many common online table level reorganizations - dbms_redefinition. Much like the dbms_metadata package, dbms_redefinition provides an almost limitless set of use cases or scenarios that it can address. Many people will probably just use the OEM graphical interface, but  here is a very common example that should fulfill as key need as well as serve as a foundation for one's own modifications. The following are the key basic steps:

1.      Verify that the table is a candidate for online redefinition

2.      Create an interim table

3.      Enable parallel DML operations

4.      Start the redefinition process (and do not stop until step 9 is done)

5.      Copy dependent objects

6.      Check for any errors

7.      Synchronize the interim table (optional)

8.      Complete the redefinition

9.      Drop the interim table

online table redefinition example

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.

In Oracle9i, 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.

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.

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

With Oracle database 10g, it is possible to use pseudo-primary keys, unique keys with NOT NULL constraints in all component columns.

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. This view is new in 10g.

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.