Don't Shut Down That Database!
Use Oracle 9i Online Object Redefinition Instead

by Chris Lawson and Roger Schrag
Database Specialists, Inc.

About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.

Introduction

Most database administrators these days are finding that the databases they manage are getting larger and more complex. At the same time, DBAs are being given less-if any-down time in order to maintain databases in smooth working order. Once upon a time we could ask for three hours on a Sunday night to reorganize a tablespace. Not anymore! Luckily, with each successive release Oracle Corporation has been giving DBAs more tools to keep databases running smoothly with less down time.

Way back in Oracle 7, the concept of dynamic instance parameters was introduced. This allowed DBAs to adjust certain instance parameters such as sort_area_size without having to boot all users off the system and restart the instance. Oracle 8i introduced online index rebuilds and online table moves (well, for index-organized tables only!). However, the ability to make substantial changes to a table definition without restricting access was still lacking.

With the first release of Oracle 9i, Oracle Corporation has introduced a new concept called "online redefinition." By calling a supplied PL/SQL package called DBMS_REDEFINITION, it is now possible to perform many types of table maintenance without taking away read or write access from users. With online redefinition, you can move a table to a new tablespace, change its storage parameters, add columns, remove columns, rename columns, change data types, change index and constraint definitions... and the list goes on.

In this paper, two experienced Oracle DBAs will introduce you to this exciting new feature. Since neither of us works for Oracle Corporation, we'd like to think this paper is balanced and objective. First we will introduce the DBMS_REDEFINITION package. Then we will give an overview of the online redefinition process followed by a few examples. Next we will cover the restrictions and limitations of this feature, and then we'll discuss lessons we've learned from using online redefinition in Oracle 9i.

The DBMS_REDEFINITION Package

The key to the online redefinition facility in Oracle 9i is a supplied PL/SQL package called DBMS_REDEFINITION. This package contains the following five procedures:

A typical online redefinition process uses the first three procedures. The ABORT_REDEF_TABLE procedure is only used to back out of a redefinition process that you began but do not wish to complete. The SYNC_INTERIM_TABLE procedure is purely optional; you may use it in order to speed up the call to the FINISH_REDEF_TABLE procedure.

The user performing the online redefinition must have permission to execute the DBMS_REDEFINITION package, as well as the system privileges CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, and SELECT ANY TABLE. As a practical matter, it seems simplest to use a DBA account when performing online redefinition.

Overview Of The Online Redefinition Process

The online redefinition process is not very complicated, but there are a series of well-defined steps that must be followed. In this section we'll walk through the five steps of the online redefinition process.

Verify The Table Is Eligible For Online Redefinition

The first step of the online redefinition process is to make sure that the table to be redefined qualifies for online redefinition. There are various requirements and limitations. For example, a table must have a declared primary key and cannot be the basis of any materialized views in order to be eligible for online redefinition. (We will discuss the restrictions and limitations in greater detail in a later section of this paper.)

You verify eligibility by calling the CAN_REDEF_TABLE procedure in the DBMS_REDEFINITION package, supplying the schema and name of the table you wish to redefine. Suppose, for example, we want to redefine the RATE table in the FINANCE schema. The call to the CAN_REDEF_TABLE procedure would look like this:

        EXECUTE dbms_redefinition.can_redef_table ('FINANCE', 'RATE');

If the procedure completes successfully without raising an exception, then the table is eligible for online redefinition. If the table is not eligible, then the procedure will raise an exception describing the problem. For example, calling the CAN_REDEF_TABLE procedure for a table that does not have a declared primary key will cause the exception "ORA-12089: Cannot online redefine table with no primary key."

Create An Interim Table

In order to avoid interfering with the production table, the online redefinition process makes use of an interim or staging table. Instead of making object definition changes directly to the production table, the changes are made to the interim table and data is copied from the production table into the interim table. At the end of the redefinition process when all data has been loaded into the interim table and you are satisfied with the results, the production table and interim table will be swapped.

At this point we need to create the interim table. Be sure to give the table the same primary key as the existing production table. However, do not create any indexes or declare any constraints on the interim table other than the primary key. Don't create database triggers, either. We will put all of these in place later on in the process.

The interim table and its primary key should be created with the exact definition and storage characteristics that are desired in the final, redefined table. If column definitions will be changed (such as column names or data types), the interim table should use the final column names and definitions. If the redefined table is to be index-organized or partitioned, then the interim table should be created that way.

Associate The Interim Table With The Table Being Redefined

Once the interim table has been created, the next step is to link the production table to it and copy the data. To do this we use the START_REDEF_TABLE procedure in the DBMS_REDEFINITION package. When calling this procedure, we simply supply the schema name along with the names of the two tables. If changes are being made to column mapping as part of the redefinition, then it will be necessary to supply an additional parameter to explain the column mapping. Column mapping will be discussed in a later section of this paper.

Suppose we are redefining the RATE table in the FINANCE schema and we have created an interim table called STAGING. Our call to the START_REDEF_TABLE procedure would look like this:

        EXECUTE dbms_redefinition.start_redef_table ('FINANCE', 'RATE', 'STAGING');

The START_REDEF_TABLE procedure will create a materialized view and log on the interim and production tables. When the materialized view is created, Oracle will copy all rows from the production table to the interim table. Although this copy could take some time if the table being redefined has a lot of rows in it, users will be able to query and update rows freely while the copy is taking place.

Add Constraints, Indexes, Triggers, And Grants To The Interim Table

At this point, all of the rows in the production table have been copied to the interim table. It is now time to add any constraints, indexes, database triggers and grants to the interim table that you wish to be present on the production table at the conclusion of the redefinition process. Note that any foreign keys that you declare on the interim table at this point should be created with the DISABLE keyword. The foreign key constraints will be enabled later in the redefinition process. Actually, foreign keys can be quite tricky. We'll talk more about how to handle them later in this paper.

Any constraint, index, trigger, or grant that exists on the production table but not on the interim table will disappear from the production table when you perform the next step of the redefinition process. Therefore it is important for you to make sure you have added all desired features to the interim table at this time.

Complete The Online Redefinition

At this point, most or perhaps all of the rows from the production table have been copied to the interim table, and a materialized view log shows what row changes still need to be copied. Furthermore, the interim table has all of the column definitions, indexes, constraints, triggers, and grants that we will want to have on the redefined table.

In order to complete the online redefinition, we call the FINISH_REDEF_TABLE procedure in the DBMS_REDEFINITION package. At this time, Oracle will read the materialized view log on the production table and make any last minute row changes to the interim table so that the production table and interim table will be exactly in sync. Then Oracle will swap the names of the two tables in the data dictionary so that the table that was the interim table is now the production table and vice versa.

Because most of the rows were copied from the production table to the interim table when the START_REDEF_TABLE procedure was called, this step of the process usually goes very quickly. Later in this paper we'll talk about what you can do if users change a large number of rows in the production table before the FINISH_REDEF_TABLE procedure is called.

Oracle will lock the two tables in exclusive mode in order to swap their names in the data dictionary, but the lockdown period will typically be extremely brief. After Oracle has completed the swap of the two tables, the materialized view and log that were created earlier will automatically be dropped. You are now free to drop the interim table (which is actually what used to be the production table before the redefinition began).

Aborting The Process

At any point before calling the FINISH_REDEF_TABLE procedure, you may abort the online redefinition. You might want to do this, for example, if you realize that you have a mistake in a column mapping or if you discover that a new data type you were planning to use is incompatible with data already in the column.

In order to terminate the redefinition process, call the ABORT_REDEF_TABLE procedure in the DBMS_REDEFINITION package. When this procedure is called, Oracle will drop the materialized view and log that were created automatically behind the scenes. If desired, you may manually drop the interim table that you created.

Examples Of Online Redefinition

In this section we'll walk through two examples in order to demonstrate the online redefinition process. In the first example, we'll move a critical table called ACCTS_PAYABLE to a different tablespace and add two new indexes. Before Oracle 9i and online redefinition, we would have had to suspend user access to the database in order to do this. (This is because the ALTER TABLE...MOVE and CREATE INDEX commands lock heap-organized tables for the duration of the operation.)

In the second example, we will rebuild a production table called INVOICES, stripping all trailing blanks off the values in one column and changing the data type of another. Recall that the ALTER TABLE command does not let you change the data type of a column unless all rows have a NULL in that column. Without the online redefinition feature we would not have been able to make these changes to the table without restricting users from accessing the INVOICES table.

Example One: Moving A Table To A New Tablespace And Adding Indexes

In this example, we will move a production table into a different tablespace and add new indexes. The column definitions are fine as-is and will not be changed.

Verify Eligibility For Online Redefinition

We call the CAN_REDEF_TABLE procedure in the DBMS_REDEFINITION package to confirm that the online redefinition process can be used on the ACCTS_PAYABLE table in the FINANCE schema:

        SQL> EXECUTE dbms_redefinition.can_redef_table ('FINANCE', 'ACCTS_PAYABLE');

        PL/SQL procedure successfully completed

        SQL>

The call completes successfully without raising an exception. This tells us that the ACCTS_PAYABLE table is suitable for online redefinition.

Create The Interim Table

We create an interim table that will ultimately become the production ACCTS_PAYABLE table when the online redefinition is complete. We create the interim table with the exact column definitions, tablespace assignment, storage clause, heap/index organization, and partitioning that we want the redefined table to have. We declare the primary key (with the desired storage clause and tablespace assignment) but do not declare any other constraints at this time. We also hold off on creating indexes and database triggers on the interim table until later.

In our example, we create the interim table with the same column names and data types as the production ACCTS_PAYABLE table. However, we specify a different tablespace so that the ACCTS_PAYABLE table will be in a new tablespace at the end of the online redefinition process:

        SQL> CREATE TABLE accts_payable_stage
          2  (
          3  account_id     VARCHAR2(20),
          4  vendor_name    VARCHAR2(50) NOT NULL,
          5  vendor_address VARCHAR2(50),
          6  vendor_po      VARCHAR2(20),
          7  invoice_date   DATE         NOT NULL,
          8  invoice_amount NUMBER       NOT NULL,
          9  CONSTRAINT accts_payable_pk PRIMARY KEY (account_id)
         10  USING INDEX TABLESPACE accts_pay_ind
         11  )
         12  TABLESPACE accts_pay_tab;
        
        Table created.
        
        SQL>

Associate The Interim Table With The Table Being Redefined

We call the START_REDEF_TABLE procedure in the DBMS_REDEFINITION package in order to begin the online redefinition of the ACCTS_PAYABLE table, using the ACCTS_PAYABLE_STAGE table as the interim table. Because we are not changing any of the column definitions, we don't need to provide column mapping information when we call the START_REDEF_TABLE procedure.

During this step Oracle will copy the contents of the ACCTS_PAYABLE table to the ACCTS_PAYABLE_STAGE table and create a materialized view log on the ACCTS_PAYABLE table in order to capture future row changes. Depending on the size of the ACCTS_PAYABLE table, this step could take some time. However, the table will not be locked-applications will still be able to query and update the ACCTS_PAYABLE table while rows are being copied.

        SQL> BEGIN
          2    dbms_redefinition.start_redef_table ('FINANCE', 'ACCTS_PAYABLE', 
          3                                         'ACCTS_PAYABLE_STAGE');
          4  END;
          5  /

        PL/SQL procedure successfully completed.	

        SQL>

Add Indexes, Constraints, Database Triggers, And Grants To The Interim Table

Now we create indexes, constraints, database triggers, and grants on the interim table that we wish to be present on the ACCTS_PAYABLE table when the online redefinition is complete. Whatever tablespace assignments and storage clauses we specify for these indexes will be what is in place on the ACCTS_PAYABLE table after the process is complete. Foreign key constraints should be created in a disabled state. If an index, constraint, trigger, or grant exists on the ACCTS_PAYABLE table but we don't create a corresponding one on the ACCTS_PAYABLE_STAGE table, then that index, constraint, trigger, or grant will no longer be present on the ACCTS_PAYABLE table when the online redefinition is completed.

In our example we create three indexes on the interim table so that they will eventually be on the redefined ACCTS_PAYABLE table. The existing ACCTS_PAYABLE table had an index on the vendor_name column, but the indexes on vendor_po and invoice_date are new:

        SQL> CREATE INDEX accts_payable_n1 ON accts_payable_stage (vendor_name)
          2  TABLESPACE accts_pay_ind;

        Index created.

        SQL> CREATE INDEX accts_payable_n2 ON accts_payable_stage (vendor_po)
          2  TABLESPACE accts_pay_ind;

        Index created.

        SQL> CREATE INDEX accts_payable_n3 ON accts_payable_stage (invoice_date)
          2  TABLESPACE accts_pay_ind;

        Index created.

        SQL>

Complete The Online Redefinition

We call the FINISH_REDEF_TABLE procedure in the DBMS_REDEFINITION package in order to complete the online redefinition of the ACCTS_PAYABLE table. Oracle will first read the materialized view log and propagate any row changes to the ACCTS_PAYABLE_STAGE table if necessary. Then Oracle will lock the ACCTS_PAYABLE table in exclusive mode, rename the ACCTS_PAYABLE_STAGE table to ACCTS_PAYABLE, rename the old ACCTS_PAYABLE table to ACCTS_PAYABLE_STAGE, and drop the materialized view log that was created during the call to the START_REDEF_TABLE procedure. Although the production table does get locked at one point during this call, the lock will be held for a very brief amount of time.

        SQL> BEGIN
          2    dbms_redefinition.finish_redef_table ('FINANCE', 'ACCTS_PAYABLE', 
          3                                          'ACCTS_PAYABLE_STAGE');
          4  END;
          5  /

        PL/SQL procedure successfully completed.	

        SQL>

The ACCTS_PAYABLE table now resides in the new tablespace and has the extra indexes. We may drop the ACCTS_PAYABLE_STAGE table at any time.

Example Two: Massaging Table Data And Changing The Data Type Of A Column

In this example, we will rebuild the INVOICES table, stripping unwanted trailing blanks from the DESCRIPTION column and changing the data type of the VENDOR_ID column from NUMBER to VARCHAR2(20). This example will demonstrate the column mapping capabilities of the online redefinition facility. Assume the current definition of the INVOICES table is:

        SQL> DESCRIBE invoices

         Name                            Null?    Type
         ------------------------------- -------- ----
         INVOICE_ID                      NOT NULL VARCHAR2(20)
         LINE_ITEM                       NOT NULL NUMBER
         DESCRIPTION                              VARCHAR2(200)
         VENDOR_ID                       NOT NULL NUMBER
         INVOICE_AMT                     NOT NULL NUMBER
        
        SQL>

The INVOICE_ID column is the primary key. The data in the DESCRIPTION column has unwanted trailing spaces from a prior data conversion that went bad. The VENDOR_ID column currently contains numeric data, but in the future there will be a need to support alphanumeric vendor IDs.

Verify Eligibility For Online Redefinition

As before, we call the CAN_REDEF_TABLE procedure in the DBMS_REDEFINITION package to confirm that the online redefinition process can be used on the INVOICES table in the FINANCE schema:

        SQL> EXECUTE dbms_redefinition.can_redef_table ('FINANCE', 'INVOICES');

        PL/SQL procedure successfully completed

        SQL>

Build The Interim Table

As in the previous example, we create an interim table that will ultimately become the production table when the online redefinition is complete.

In this example, we create the interim table with the same column names as the production INVOICES table since these will not be changing. However, the vendor_id column in the interim table has a VARCHAR2(20) data type instead of NUMBER because we are changing the data type for this column. And even though we are not moving the INVOICES table to a new tablespace, we still specify the name of the tablespace so that the redefined INVOICES table will be in the correct tablespace at the end of the online redefinition process:

        SQL> CREATE TABLE invoices_stage
          2  (
          3  invoice_id     VARCHAR2(20),
          4  line_item      NUMBER       NOT NULL,
          5  description    VARCHAR2(200),
          6  vendor_id      VARCHAR2(20) NOT NULL,
          7  invoice_amt    NUMBER       NOT NULL,
          8  CONSTRAINT invoices_pk PRIMARY KEY (invoice_id)
          9  USING INDEX TABLESPACE ind
         10  )
         11  TABLESPACE tab;
        
        Table created.
        
        SQL>

Associate The Interim Table With The Table Being Redefined

As in the previous example, we call the START_REDEF_TABLE procedure in the DBMS_REDEFINITION package in order to begin the online redefinition process. This time, however, we must provide column mapping information for all columns because at least one column is being changed. The column map looks like a SELECT list; it tells Oracle how to fetch the data from the INVOICES table in order to populate the INVOICES_STAGE table. We will talk about column maps and how to format them in greater detail later in this paper. For now, note the use of the RTRIM function to strip off the unwanted trailing blanks, and the use of the TO_CHAR function to convert numeric data to character data:

        SQL> BEGIN
          2   dbms_redefinition.start_redef_table ('FINANCE', 'INVOICES', 'INVOICES_STAGE',
          3     'INVOICE_ID INVOICE_ID, LINE_ITEM LINE_ITEM, ' ||
          4     'RTRIM (DESCRIPTION) DESCRIPTION, TO_CHAR (VENDOR_ID) VENDOR_ID, ' ||
          5     'INVOICE_AMT INVOICE_AMT');
          6  END;
          7  /

        PL/SQL procedure successfully completed.	

        SQL>

Add Indexes, Constraints, Database Triggers And Grants To The Interim Table

For simplicity, the INVOICES table in this example does not have any indexes, constraints, database triggers, or grants other than the primary key. Since we already declared the primary key when we created the interim table, we don't need to create any additional database objects in this step.

Complete The Online Redefinition

As in the previous example, we call the FINISH_REDEF_TABLE procedure in the DBMS_REDEFINITION package in order to complete the online redefinition of the INVOICES table:

        SQL> BEGIN
          2    dbms_redefinition.finish_redef_table ('FINANCE', 'INVOICES', 'INVOICES_STAGE');
          3  END;
          4  /

        PL/SQL procedure successfully completed.	

        SQL>

The DESCRIPTION column of the INVOICES table is now free of the unwanted trailing blanks, and the VENDOR_ID column now has a data type of VARCHAR2(20) instead of NUMBER. Describing the table in SQL*Plus confirms this:

        SQL> DESCRIBE invoices

         Name                            Null?    Type
         ------------------------------- -------- ----
         INVOICE_ID                      NOT NULL VARCHAR2(20)
         LINE_ITEM                       NOT NULL NUMBER
         DESCRIPTION                              VARCHAR2(200)
         VENDOR_ID                       NOT NULL VARCHAR2(20)
         INVOICE_AMT                     NOT NULL NUMBER

        SQL>

Restrictions

When considering which tables to redefine online, remember that there are some restrictions. Certain tables are not eligible, and there are limitations on column remapping as well. Also, filtering out some rows from the table is not possible.

Table Eligibility

Most tables will be eligible for online redefinition. However there are some specific restrictions. The most basic requirement is that the table to be redefined must have a declared primary key. Remember also that the interim table must be created with a primary key that matches the table being redefined.

Tables that are the source of a materialized view or are part of a cluster are not eligible for online redefinition. Also, temporary tables and tables in the SYS or SYSTEM schema are ineligible. Tables that have columns with FILE or LONG data types, or user-defined data types cannot be rebuilt online.

Tables with foreign keys that reference other tables, and tables that are referenced by foreign keys on other tables are eligible for online redefinition. However, there are some special things to watch out for when redefining tables involved in foreign key relationships, and Oracle's documentation does not explain this as well as it could. Foreign keys are covered a bit more in a later section of this paper.

Column Remapping Limitations

When remapping a column, only a simple function or expression may be used. For instance, applying a function to change the case of a string of text, adding or removing characters, or performing arithmetic on a numeric value would all be allowable column remappings. Subqueries and non-deterministic functions, however, cannot be used when remapping a column.

If a new column not derived from existing columns is being added to the table, this column cannot be defined as NOT NULL. Instead, initially add the column as nullable and make the column NOT NULL after the redefinition is complete and the column has been populated.

The column remapping feature of online redefinition can be used to rename or drop columns from a table.

Row Selectivity

Online redefinition cannot be used to eliminate some of the rows from the table being redefined. While you can use column remapping to remove columns from a table, you cannot remove rows. You can, however, redefine an unpartitioned table into a partitioned table. Perhaps you could work around this limitation by redefining a table into a partitioned table, directing all rows you wish to eliminate into one partition and dropping that partition after the redefinition is complete.

Lessons Learned

Although the basic principles of online redefinition are simple, there are some aspects that are a little tricky or require special handling. Also, Oracle's documentation on this new feature in Oracle 9i is pretty scant. Perhaps the documentation provided with a future release of Oracle 9i will be more thorough. Following are some key lessons we've learned about online redefinition.

Testing With The Interim Table

One significant feature of online redefinition is the ability to confirm that the new table definition will be correct prior to actually activating it in the production system. This is accomplished by subjecting the interim table to all tests that would normally be applied to the production table. For example, you can run queries against the interim table to verify that column remapping or data massaging yielded the exact data you expected.

Testing can commence after the START_REDEF_TABLE procedure has been invoked and all constraints, triggers, and indexes have been built on the interim table. At this point the interim table is ready to be "swapped" with the production table, but it would be wise to perform some checks before running the final procedure, FINISH_REDEF_TABLE.

Constraint Validation

The Oracle documentation omits a very important point regarding referential integrity. When the FINISH_REDEF_TABLE procedure completes, all foreign key constraints will be in a non-validated state. This feature does have the advantage of avoiding the extra time (and locking) that would be required to validate the foreign keys. However, the unwary DBA may be fooled into thinking that the constraints, once they are enabled, have been validated.

Remember that, beginning in Oracle 8i, you can enable a constraint without validating the existing rows in the table. After an online redefinition, foreign key constraints will be enabled but existing rows will not be validated. If you want to be sure no rows in the table violate the constraint, you'll need to validate the constraint manually.

Handling Foreign Keys

Recall that the online redefinition process requires that we add all constraints to the interim table that we want to be present on the redefined table. So, if we plan to redefine a table with a foreign key and we want to keep that foreign key, we will need to create a foreign key constraint on the interim table after calling the START_REDEF_TABLE procedure. Then, after the FINISH_REDEF_TABLE procedure is called, this constraint will be present on the redefined production table.

But what if we want to redefine a table that is a parent to another table's foreign key constraint? This presents a tricky problem, because the foreign key constraint is defined on the child table, not the parent table. At the end of the online redefinition of the parent table, the child table's foreign key constraint will reference the interim table and not the redefined production table. When the interim table is dropped (with the CASCADE CONSTRAINTS option) the child table's foreign key constraint will be dropped as well.

The only way to redefine a table that is a parent to another table's foreign key without losing the foreign key relationship seems to be to create a second foreign key constraint on the child table that references the interim table. You will need to create this new constraint with the DISABLE keyword, and only after calling the START_REDEF_TABLE procedure. When the FINISH_REDEF_TABLE procedure is called and the online redefinition completes, this new foreign key constraint will now reference the redefined production table.

This is slightly unpleasant because it puts you in the position of having to add new foreign key constraints to child production tables in order to redefine parent tables online. You will not be able to enable these new constraints and validate all existing rows in the child tables without consuming a lot of system resources.

Column Mapping

When calling the START_REDEF_TABLE procedure, you must provide the name of the table to be redefined, the name of the interim table, and the schema where the two tables are located. If the redefinition will alter the column mapping in any way, then a fourth parameter must be provided which describes the complete mapping between the columns of the production table and the interim table. The syntax of this column mapping parameter can be a bit tricky.

The column mapping is one VARCHAR2 string consisting of a comma-delimited list of expressions and column names. For each column that you want in the redefined table, include an expression that defines how to derive the data from the existing production table and the name of a column in the interim table to receive this data.

Remember that VARCHAR2 strings are delimited by single quotes. You can include a single quote in your string by putting two single quotes consecutively in the string. The column mapping string will likely be quite long. For readability, you might want to break the string up into multiple pieces on separate lines of a script. You can do this by using the hyphen continuation character in SQL*Plus, or by using the string concatenation operator ||.

For example, suppose that we are redefining the COMMISSION table in the FINANCE schema. In the process we need to increase the value in the RATE column by 10%. There are just three columns in this table, so the column mapping will consist of three expression/column name pairings, delimited by commas and enclosed in single quotes. Note how the 10% rate increase is indicated. In this example we use the hyphen continuation character in SQL*Plus:

        SQL> EXECUTE dbms_redefinition.start_redef_table -
        >    ('FINANCE', 'COMMISSION', 'STAGING', -
        >     'SALESPERSON_ID SALESPERSON_ID, REGION REGION, RATE * 1.10 RATE');

        PL/SQL procedure successfully completed.	

        SQL>

As another example, suppose we are redefining the INVOICES table in the FINANCE schema. In the process we need to add an "A" to the end of every invoice number in the system. For simplicity we will pretend there are just five columns in the table. Note the use of consecutive single quotes to put a string of text inside the column remapping parameter. In this example we use the string concatenation operator || to wrap the procedure call over multiple lines:

        SQL> BEGIN
          2    dbms_redefinition.start_redef_table
          3    ('FINANCE', 'INVOICES', 'INVOICES_INTERIM', 
          4     'INVOICE_ID INVOICE_ID, VENDOR_ID VENDOR_ID, INVOICE_DATE INVOICE_DATE, ' ||
          5     'AMOUNT AMOUNT, INVOICE_NUMBER || ''A'' INVOICE_NUMBER');
          6  END;
          7  /

        PL/SQL procedure successfully completed.	

        SQL>

Speeding Up The FINISH_REDEF_TABLE Call

During the online redefinition process, a materialized view log is used to keep track of changes made to the production table so that these changes can be applied to the interim table as well. This is how Oracle ensures that no updates are lost even if users are writing to a table while it is being redefined online. The materialized view and log are automatically created when the START_REDEF_TABLE procedure is called, and dropped when the FINISH_REDEF_TABLE or ABORT_REDEF_TABLE procedures are called.

When the FINISH_REDEF_TABLE procedure is called, Oracle has to update the interim table with any changes that were made to the production table that have not yet been propagated to the interim table. If there have been a large number of changes to the production table, it may take some time for this propagation to complete. This could happen if the table being redefined is very active, or if a long period of time is spent testing the data in the interim table before completing the redefinition.

Delays in the final synchronization can be avoided by calling the SYNC_INTERIM_TABLE procedure. This procedure will propagate all pending changes from the production table to the interim table. Behind the scenes, Oracle is simply refreshing the materialized view that it created. You may call the SYNC_INTERIM_TABLE procedure as many times as you like after the START_REDEF_TABLE procedure has completed and before calling the FINISH_REDEF_TABLE procedure.

Although using the SYNC_INTERIM_TABLE procedure will not speed up the overall online redefinition process, it will speed up the call to the FINISH_REDEF_TABLE procedure-which is where the critical completion steps of the redefinition take place.

Conclusion

The online redefinition feature of Oracle 9i is a welcome addition that DBAs will appreciate and grow to rely upon. Oracle Corporation certainly recognizes a need from its customers to be able to keep systems available while maintenance procedures are being performed. The DBMS_REDEFINITION package is an important step toward increased availability. This package is about as easy to use as it could possibly be. There are some complicated nuances, such as the handling of foreign keys, but these stem from the complexity of the challenge (redefining a table while users are writing to it) and not from the solution that Oracle has implemented.

Online redefinition is new in Oracle 9i. As with any new feature in Oracle, DBAs should test thoroughly to make sure that everything works exactly as expected without undesirable side effects. The documentation set that comes with the first release of Oracle 9i has surprisingly little to say about online redefinition, and as of this writing extremely little has surfaced on Metalink either. With future releases of Oracle 9i, we are hopeful for more thorough documentation and an improvement of some of the limitations of online redefinition.

About The Authors

Chris Lawson is an Oracle DBA consultant in the San Francisco Bay area, where he specializes in performance tuning of data warehouse and financial applications. He is a frequent speaker at the Northern California Oracle Users Group, and has written for a number of publications such as Oracle Internals, Exploring Oracle, SELECT, Oracle Informant, and Intelligent Enterprise. Chris has held a variety of positions in the IT field, ranging from systems engineer to department manager. He is also an instructor for the University of Phoenix. Chris can be contacted via http://www.oraclemagician.com.

Roger Schrag has been an Oracle DBA and application architect for over twelve years, starting out at Oracle Corporation on the Oracle Financials development team and moving on to production DBA and database architect at various companies in the San Francisco Bay Area. Roger is a frequent speaker at Oracle OpenWorld and the IOUG-A Live conferences. He is also Vice President of the Northern California Oracle Users Group. In 1995, Roger founded Database Specialists, Inc., a consulting firm specializing in business solutions based on Oracle technology. The company was recently named one of the Top 150 Fastest-Growing Private Companies in the Bay Area by the San Francisco Business Times.

Still Looking for Help on this Subject?

Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
Complimentary Newsletter
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.


Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com