Redefining Tables Online - Oracle 10g

In highly available systems, it is occasionally necessary to redefine large "hot" tables to improve the performance of queries or DML performed against these tables. The database provide a mechanism to redefine tables online. This mechanism provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline.

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

This section contains the following topics:

  • Features of Online Table Redefinition
  • Steps for Online Redefinition of Tables
  • Intermediate Synchronization
  • Terminate and Clean Up After Errors
  • Example of Online Table Redefinition
  • Restrictions for Online Redefinition of Tables

Features of Online Table Redefinition

Online table redefinition enables you to:

  • Modify the storage parameters of the table
  • Move the table to a different tablespace in the same schema
  • Add support for parallel queries
  • Add or drop partitioning support
  • Re-create the table to reduce fragmentation
  • Change the organization of a normal table (heap organized) to an index-organized table and vice versa
  • Add or drop a column


The mechanism for performing online redefinition is the PL/SQL package DBMS _ REDEFINITION. Execute privileges on this package is granted to EXECUTE_ CATALOG _ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:


Steps for Online Redefinition of Tables

In order to perform an online redefinition of a table the user must perform the following steps.

  1. Choose one of the following two methods of redefinition:
    • The first method of redefinition is to use the primary keys or pseudo- primary keys to perform the redefinition. Pseudo- primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.
    • The second method of redefinition is to use rowids. For this method, the table to be redefined should not be an index organized table. Also, in this method of redefinition, a hidden column named M _ROW$$ is added to the post- redefined version of the table and it is recommended that this column be marked as unused or dropped after the redefinition is completed.
  2. Verify that the table can be online redefined by invoking the DBMS _REDEFINITION.CAN _REDEF _TABLE() procedure and use the OPTIONS _FLAG parameter to specify the method of redefinition to be used. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be online redefined.
  3. Create an empty interim table (in the same schema as the table to be redefined) with all of the desired attributes. If columns are to be dropped, do not include them in the definition of the interim table. If a column is to be added, then add the column definition to the interim table.

    It is possible to perform table redefinition in parallel. If you specify a degree of parallelism on both of the tables and you ensure that parallel execution is enabled for the session, the database will use parallel execution whenever possible to perform the redefinition. You can use the PARALLEL clause of the ALTER SESSION statement to enable parallel execution.

  4. Start the redefinition process by calling DBMS_REDEFINITION.START_REDEF_TABLE(), providing the following:
    • The table to be redefined
    • The interim table name
    • The column mapping
    • The method of redefinition
    • Optionally, the columns to be used in ordering rows
    • Optionally, specify the ORDER BY columns

    If the column mapping information is not supplied, then it is assumed that all the columns (with their names unchanged) are to be included in the interim table. If the column mapping is supplied, then only those columns specified explicitly in the column mapping are considered. If the method of redefinition is not specified, then the default method of redefinition using primary keys is assumed.

    You can optionally specify the ORDERBY _COLS parameter to specify how rows should be ordered during the initial instantiation of the interim table.

  5. You have two methods for creating (cloning) dependent objects such as triggers, indexes, grants, and constraints on the interim table. Method 1 is the most automatic and preferred method, but there may be times that you would choose to use method 2.
    • Method 1: Automatically Creating Dependent Objects

      Use the COPY _TABLE _DEPENDENTS procedure to automatically create dependent objects such as triggers, indexes, grants, and constraints on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their cloned counterparts to be automatically swapped later as part of the redefinition completion process. The result is that when the redefinition is completed, the names of the dependent objects will be the same as the names of the original dependent objects.

      You can discover if errors occurred while copying dependent objects by checking the NUM_ERRORS output variable. If the IGNORE_ERRORS parameter is set to TRUE, the COPY _TABLE _DEPENDENTS procedure continues cloning dependent objects even if an error is encounter when creating an object. The errors can later be viewed by querying the DBA _REDIFINITION _ERRORS view. Reasons for errors include a lack of system resources or a change in the logical structure of the table.

      If IGNORE_ERRORS is set to FALSE, the COPY _TABLE _DEPENDENTS procedure stops cloning objects as soon as any error is encountered.

      After you correct any errors you can attempt again to clone the failing object or objects by reexecuting the COPY _TABLE _DEPENDENTS procedure. Optionally you can create the objects manually and then register them as explained in method 2.

      The COPY _TABLE _DEPENDENTS procedure can be used multiple times as necessary. If an object has already been successfully cloned, it will ignore the operation.

  6. Method 2: Manually Creating Dependent Objects

    You can manually create dependent objects on the interim table. Use the REGISTER _DEPENDENT _OBJECT procedure after you create dependent objects manually. You can also use the COPY _TABLE _ DEPENDENTS procedure to do the registration. Note that the COPY _TABLE _DEPENDENTS procedure does not clone objects that are registered manually. You would also use the REGISTER _DEPENDENT _OBJECT procedure if the COPY _TABLE _DEPENDENTS procedure failed to copy a dependent object and manual intervention is required.

    You can query the DBA _REDEFINITION _OBJECTS view to determine which dependent objects are registered. This view shows dependent objects that were registered explicitly with the REGISTER _DEPENDENT _OBJECT procedure or implicitly with the COPY _TABLE _DEPENDENTS procedure. Only current information is shown in the view.

    The UNREGISTER_DEPENDENT_OBJECT procedure can be used to unregister a dependent object on the table being redefined and on the interim table.

  7. Execute the DBMS _REDEFINITION.FINISH _REDEF _TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in the exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH _REDEF _TABLE will wait for all pending DML that was initiated before it was invoked to commit before completing the redefinition. As a result of this procedure, the following occur:
    1. The original table is redefined such that it has all the attributes, indexes, constraints, grants and triggers of the interim table
    2. The referential constraints involving the interim table now involve the post redefined table and are enabled.
    3. Dependent objects that were registered, either explicitly using REGISTER _DEPENDENT _OBJECT or implicitly using COPY _TABLE _DEPENDENTS, are renamed automatically.
  8. If the redefinition was done using rowids, the post-redefined table will have a hidden column (M_ROW$$) and it is recommended that the user set this hidden column to unused as follows:

The following is the end result of the redefinition process:

  • The original table is redefined with the attributes and features of the interim table.
  • The triggers, grants, indexes and constraints defined on the interim table after START _REDEF _TABLE() and before FINISH _REDEF _TABLE() are now defined on the post- redefined table. Any referential constraints involving the interim table before the redefinition process was finished now involve the post-redefinition table and are enabled.
  • Any indexes, triggers, grants and constraints defined on the original table (prior to redefinition) are transferred to the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled.
  • Any PL/SQL procedures and cursors defined on the original table (prior to redefinition) are invalidated. They are automatically revalidated (this

Intermediate Synchronization

After the redefinition process has been started by calling START _REDEF _TABLE() and before FINISH _REDEF _TABLE() has been called, it is possible that a large number of DML statements have been executed on the original table. If you know this is the case, it is recommended that you periodically synchronize the interim table with the original table. This is done by calling the DBMS _REDEFINITION.SYNC _INTERIM _TABLE() procedure. Calling this procedure reduces the time taken by FINISH _REDEF _TABLE() to complete the redefinition process.

The small amount of time that the original table is locked during FINISH _REDEF _TABLE() is independent of whether SYNC _INTERIM _TABLE() has been called.

Terminate and Clean Up After Errors

In the event that an error is raised during the redefinition process, or if you choose to terminate the redefinition process, call DBMS _REDEFINITION.ABORT _REDEF_TABLE(). This procedure drops temporary logs and tables associated with the redefinition process. After this procedure is called, you can drop the interim table and its associated objects.

Example of Online Table Redefinition

This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:

  • New columns mgr, hiredate, sal, and bonus (these existed in the original table but were dropped in previous examples) are added.
  • The new column bonus is initialized to 0
  • The column deptno has its value increased by 10.
  • The redefined table is partitioned by range on empno.

The steps in this redefinition are illustrated below.

  1. Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
  2. Create an interim table hr.int_admin_emp.
  3. Start the redefinition process.

  4. Automatically create any triggers, indexes and constraints on hr.int_admin_emp.
  5. Optionally, synchronize the interim table hr.int_admin_emp.
  6. Complete the redefinition.

    The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the _admin _emp table.

  7. Drop the interim table.

Restrictions for Online Redefinition of Tables

The following restrictions apply to the online redefinition of tables:

  • If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not null constraints), then the table to be redefined must have the same primary key or pseudo- primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.
  • Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
  • The overflow table of an index-organized table cannot be online redefined.
  • Tables with user-defined types (objects, REFs, collections, typed tables) cannot be online redefined.
  • Tables with BFILE columns cannot be online redefined.
  • Tables with LONG columns can be online redefined, but those columns must be converted to CLOBS. Tables with LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
  • The table to be redefined cannot be part of a cluster.
  • Tables in the SYS and SYSTEM schema cannot be online redefined.
  • Temporary tables cannot be redefined.
  • A subset of rows in the table cannot be redefined.
  • Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
  • If new columns (which are not instantiated with existing data for the original table) are being added as part of the redefinition, then they must not be declared NOT NULL until the redefinition is complete.
  • There cannot be any referential constraints between the table being redefined and the interim table.
  • Table redefinition cannot be done NOLOGGING.
  • Tables with materialized view logs defined on them cannot be online redefined.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics