Redefining Tables Online

from http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN11678

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:

• Improve the performance of queries or DML

• Accommodate application changes

• Manage storage

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

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

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

You can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.

Note:

To invoke the Reorganize Objects wizard:

1. On the Tables page of Enterprise Manager, click in the Select column to select the table to redefine.

2. In the Actions list, select Reorganize.

3. Click Go.

This section describes online redefinition with the DBMS_REDEFINITION package. It contains the following topics:

• Features of Online Table Redefinition

• Performing Online Redefinition with DBMS_REDEFINITION

• Results of the Redefinition Process

• Performing Intermediate Synchronization

• Aborting Online Table Redefinition and Cleaning Up After Errors

• Restrictions for Online Redefinition of Tables

• Online Redefinition of a Single Partition

• Online Table Redefinition Examples

• Privileges Required for the DBMS_REDEFINITION Package

See Also:

Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_REDEFINITION package

Features of Online Table Redefinition

Online table redefinition enables you to:

• Modify the storage parameters of a table or cluster

• Move a table or cluster to a different tablespace

  Note:

  If it is not important to keep a table available for DML when moving it to another tablespace, you can use the simplerALTER TABLE MOVE command. See "Moving a Table to a New Segment or Tablespace".

• Add, modify, or drop one or more columns in a table or cluster

• Add or drop partitioning support (non-clustered tables only)

• Change partition structure

• Change physical properties of a single table partition, including moving it to a different tablespace in the same schema

• Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table

• Add support for parallel queries

• Re-create a table or cluster to reduce fragmentation

  Note:

  In many cases, online segment shrink is an easier way to reduce fragmentation. See "Reclaiming Wasted Space".

• Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.

• Convert a relational table into a table with object columns, or do the reverse.

• Convert an object table into a relational table or a table with object columns, or do the reverse.

Performing Online Redefinition with DBMS_REDEFINITION

You use the DBMS_REDEFINITION package to perform online redefinition of a table. See Oracle Database PL/SQL Packages and Types Reference for package details.

To redefine a table online:

1. Choose the redefinition method: by key or by rowid

   By key—Select a primary key or pseudo-primary key to use for 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.

   By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

   You cannot use this method on index-organized tables.

2. Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.

3. Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical 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. If a column is to be modified, create it in the interim table with the properties that you want.

   It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined, because these will be defined in step 6 when you copy dependent objects.

4. (Optional) If you are redefining a large table and want to improve the performance of the next step by running it in parallel, issue the following statements:

   alter session force parallel dml parallel degree-of-parallelism;
   alter session force parallel query parallel degree-of-parallelism;
   

5. Start the redefinition process by calling START_REDEF_TABLE, providing the following:

   • The schema and table name of the table to be redefined

   • The interim table name

   • A column mapping string that maps the columns of table to be redefined to the columns of the interim table

     See "Constructing a Column Mapping String" for details.

   • The redefinition method

     Package constants are provided for specifying the redefinition method. DBMS_REDEFINITION.CONS_USE_PK is used to indicate that the redefinition should be done using primary keys or pseudo-primary keys. DBMS_REDEFINITION.CONS_USE_ROWID is use to indicate that the redefinition should be done using rowids. If this argument is omitted, the default method of redefinition (CONS_USE_PK) is assumed.

   • Optionally, the columns to be used in ordering rows

   • If redefining only a single partition of a partitioned table, the partition name

   Because this process involves copying data, it may take a while. The table being redefined remains available for queries and DML during the entire process.

   Note:

   If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

6. Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods. Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.

   • Method 1: Automatically Creating Dependent Objects

     Use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their copied 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.

     For more information, see "Creating Dependent Objects Automatically".

   • Method 2: Manually Creating Dependent Objects

     You can manually create dependent objects on the interim table and then register them. For more information, see "Creating Dependent Objects Manually".

     Note:

     In Oracle Database Release 9i, you were required to manually create the triggers, indexes, grants, and constraints on the interim table, and there may still be situations where you want to or must do so. In such cases, any referential constraints involving the interim table (that is, the interim table is either a parent or a child table of the referential constraint) must be created disabled. When online redefinition completes, the referential constraint is automatically enabled. In addition, until the redefinition process is either completed or aborted, any trigger defined on the interim table does not execute.

7. Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in 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 to commit before completing the redefinition.

8. If you used rowids for the redefinition and your COMPATIBLE initialization parameter is set to 10.1.0 or lower, drop or set UNUSED the hidden columnM_ROW$$ that is now in the redefined table.

   ALTER TABLE table_name SET UNUSED (M_ROW$$);
   

   If COMPATIBLE is 10.2.0 or higher, this hidden column is automatically set UNUSED when redefinition completes. You can then drop the column with theALTER TABLE ... DROP UNUSED COLUMNS statement.

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

   If you drop the interim table while there are active queries running against it, you may encounter an ORA-08103 error ("object no longer exists").

See Also:

"Online Table Redefinition Examples"

Constructing a Column Mapping String

The column mapping string that you pass as an argument to START_REDEF_TABLE contains a comma-separated list of column mapping pairs, where each pair has the following syntax:

[expression]  column_name

The column_name term indicates a column in the interim table. The optional expression can include columns from the table being redefined, constants, operators, function or method calls, and so on, in accordance with the rules for expressions in a SQL SELECT statement. However, only simple deterministic subexpressions—that is, subexpressions whose results do not vary between one evaluation and the next—plus sequences and SYSDATE can be used. No subqueries are permitted. In the simplest case, the expression consists of just a column name from the table being redefined.

If an expression is present, its value is placed in the designated interim table column during redefinition. If the expression is omitted, it is assumed that both the table being redefined and the interim table have a column named column_name, and the value of that column in the table being redefined is placed in the same column in the interim table.

For example, if the override column in the table being redefined is to be renamed to override_commission, and every override commission is to be raised by 2%, the correct column mapping pair is:

override*1.02  override_commission

If you supply '*' or NULL as the column mapping string, it is assumed that all the columns (with their names unchanged) are to be included in the interim table. Otherwise, only those columns specified explicitly in the string are considered. The order of the column mapping pairs is unimportant.

For examples of column mapping strings, see "Online Table Redefinition Examples".

Data Conversions When mapping columns, you can convert data types, with some restrictions.

If you provide '*' or NULL as the column mapping string, only the implicit conversions permitted by SQL are supported. For example, you can convert fromCHAR to VARCHAR2 , from INTEGER to NUMBER, and so on.

If you want to perform other data type conversions, including converting from one object type to another or one collection type to another, you must provide a column mapping pair with an expression that performs the conversion. The expression can include the CAST function, built-in functions like TO_NUMBER, conversion functions that you create, and so on.

Creating Dependent Objects Automatically

You use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table.

You can discover if errors occurred while copying dependent objects by checking the num_errors output argument. If the ignore_errors argument is set toTRUE, the COPY_TABLE_DEPENDENTS procedure continues copying dependent objects even if an error is encountered when creating an object. You can view these errors by querying the DBA_REDEFINITION_ERRORS view.

Reasons for errors include:

• A lack of system resources

• A change in the logical structure of the table that would require recoding the dependent object.

  See Example 3 in "Online Table Redefinition Examples" for a discussion of this type of error.

If ignore_errors is set to FALSE, the COPY_TABLE_DEPENDENTS procedure stops copying objects as soon as any error is encountered.

After you correct any errors you can again attempt to copy the dependent objects by reexecuting the COPY_TABLE_DEPENDENTS procedure. Optionally you can create the objects manually and then register them as explained in "Creating Dependent Objects Manually". The COPY_TABLE_DEPENDENTS procedure can be used multiple times as necessary. If an object has already been successfully copied, it is not copied again.

Creating Dependent Objects Manually

If you manually create dependent objects on the interim table with SQL*Plus or Enterprise Manager, you must then use the REGISTER_DEPENDENT_OBJECTprocedure to register the dependent objects. Registering dependent objects enables the redefinition completion process to restore dependent object names to what they were before redefinition.

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.

Note:

Manually created dependent objects do not have to be identical to their corresponding original dependent objects. For example, when manually creating a materialized view log on the interim table, you can log different columns. In addition, the interim table can have more or fewer dependent objects.

Results of the Redefinition Process

The following are the end results of the redefinition process:

• The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table.

• Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

  Note:

  If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

• The referential constraints involving the interim table now involve the redefined table and are enabled.

• Any indexes, triggers, materialized view logs, 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.

• Some PL/SQL objects, views, synonyms, and other table-dependent objects may become invalidated. Only those objects that depend on elements of the table that were changed are invalidated. For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See Oracle Database Concepts for more information about schema object dependencies.

Performing 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 that this is the case, it is recommended that you periodically synchronize the interim table with the original table. This is done by calling the SYNC_INTERIM_TABLE procedure. Calling this procedure reduces the time taken byFINISH_REDEF_TABLE to complete the redefinition process. There is no limit to the number of times that you can call SYNC_INTERIM_TABLE.

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.

Aborting Online Table Redefinition and Cleaning 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 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 dependent objects.

If the online redefinition process must be restarted, if you do not first call ABORT_REDEF_TABLE, subsequent attempts to redefine the table will fail.

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 nullconstraints), then the post-redefinition table 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.

• After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

• 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 redefined online independently.

• Tables with fine-grained access control (row-level security) cannot be redefined online.

• Tables with BFILE columns cannot be redefined online.

• Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted toBLOBS. Tables with LOB columns are acceptable.

• On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to a LOB column can be executed in parallel, provided that:

  • The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

  • There is a simple mapping from one LONG column to one LOB column, and the interim table has only one LOB column.

  In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.

• Tables in the SYS and SYSTEM schema cannot be redefined online.

• 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 are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOTNULL 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.

• For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.

• You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

Online Redefinition of a Single Partition

Beginning with Oracle Database 10g Release 2, you can redefine online a single partition of a table. This is useful if, for example, you want to move a partition to a different tablespace and keep the partition available for DML during the operation.

Another use for this capability is redefining an entire table, but doing it one partition at a time to reduce resource requirements. For example, if you want to move a very large table to a different tablespace, you can move it one partition at a time to minimize the free space and undo space required to complete the move. Be aware, however, that when you redefine a single partition, if a global index is present, it is marked as UNUSABLE when redefinition is complete.

Redefining a single partition differs from redefining a table in the following ways:

• There is no need to copy dependent objects. It is not valid to use the COPY_TABLE_DEPENDENTS procedure when redefining a single partition.

• You must manually create any local indexes on the interim table.

• The column mapping string for START_REDEF_TABLE must be NULL.

• When using the by-rowid method, the final phase of redefinition drops the hidden column M_ROW$$ instead of setting it unused.

Note:

If it is not important to keep a partition available for DML when moving it to another tablespace, you can use the simplerALTER TABLE MOVE PARTITION command.

See also:

• The section "Moving Partitions" in Oracle Database VLDB and Partitioning Guide

• Oracle Database SQL Language Reference

Rules for Online Redefinition of a Single Partition

The underlying mechanism for redefinition of a single partition is the exchange partition capability of the database (ALTER TABLE...EXCHANGE PARTITION). Rules and restrictions for online redefinition of a single partition are therefore governed by this mechanism. Here are some general restrictions:

• No logical changes (such as adding or dropping a column) are permitted.

• No changes to the partitioning method (such as changing from range partitioning to hash partitioning) are permitted.

• If a global index is present, it is marked as UNUSABLE when redefinition of any table partition is complete.

Here are the rules for defining the interim table:

• If the partition being redefined is a range, hash, or list partition, the interim table must be non-partitioned.

• If the partition being redefined is a range partition of a composite range-hash partitioned table, the interim table must be a hash partitioned table. In addition, the partitioning key of the interim table must be identical to the subpartitioning key of the range-hash partitioned table, and the number of partitions in the interim table must be identical to the number of subpartitions in the range partition being redefined.

• If the partition being redefined is a range partition of a composite range-list partitioned table, the interim table must be a list partitioned table. In addition, the partitioning key of the interim table must be identical to the subpartitioning key of the range-list partitioned table, and the values lists of the interim table's list partitions must exactly match the values lists of the list subpartitions in the range partition being redefined.

• If you define the interim table as compressed, you must do one of the following:

  • Use the by-key method of redefinition, not the by-rowid method.

  • If the row-id method is unavoidable, define the interim table as COMPRESS FOR ALL OPERATIONS.

These additional rules apply if the table being redefined is a partitioned index-organized table:

• The interim table must also be index-organized.

• The original and interim tables must have primary keys on the same columns, in the same order.

• If key compression is enabled, it must be enabled for both the original and interim tables, with the same prefix length.

• Both the original and interim tables must have overflow segments, or neither can have them. Likewise for mapping tables.

• Both the original and interim tables must have identical storage attributes for any LOB columns.

See Also:

The section "Exchanging Partitions" in Oracle Database VLDB and Partitioning Guide

Online Table Redefinition Examples

For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.

Example	Description
Example 1	Redefines a table by adding new columns and adding partitioning.
Example 2	Demonstrates redefinition with object datatypes.
Example 3	Demonstrates redefinition with manually registered dependent objects.
Example 4	Redefines a single table partition, moving it to a different tablespace.

Example 1

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 are added. (These existed in the original table but were dropped in previous examples.)

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

   BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
         DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

2. Create an interim table hr.int_admin_emp.

   CREATE TABLE hr.int_admin_emp
           (empno      NUMBER(5) PRIMARY KEY,
            ename      VARCHAR2(15) NOT NULL,
            job        VARCHAR2(10),
            mgr        NUMBER(5),
            hiredate   DATE DEFAULT (sysdate),
            sal        NUMBER(7,2),
            deptno     NUMBER(3) NOT NULL,
            bonus      NUMBER (7,2) DEFAULT(1000))
        PARTITION BY RANGE(empno)
          (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
           PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
   

3. Start the redefinition process.

   BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
          'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
           dbms_redefinition.cons_use_pk);
   END;
   /
   

4. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_admin_emp.)

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
      DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
   END;
   

   Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

5. Query the DBA_REDEFINITION_ERRORS view to check for errors.

   SQL> select object_name, base_table_name, ddl_txt from
            DBA_REDEFINITION_ERRORS;
    
   OBJECT_NAME   BASE_TABLE_NAME  DDL_TXT
   ------------- ---------------- ------------------------------
   SYS_C005836   ADMIN_EMP        CREATE UNIQUE INDEX "HR"."TMP$
                                  $_SYS_C0058360" ON "HR"."INT_A
                                  DMIN_EMP" ("EMPNO")
    
   SYS_C005836   ADMIN_EMP        ALTER TABLE "HR"."INT_ADMIN_EM
                                  P" ADD CONSTRAINT "TMP$$_SYS_C
                                  0058360" PRIMARY KEY
   

   These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.

   Note:

   The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects withCOPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.

6. Optionally, synchronize the interim table hr.int_admin_emp.

   BEGIN 
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
   END;
   /
   

7. Complete the redefinition.

   BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
   END;
   /
   

   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 hr.int_admin_emp table.

8. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 2

This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.

The original table, named CUSTOMER, is defined as follows:

Name         Type          
------------ ------------- 
CID          NUMBER            <- Primary key
NAME         VARCHAR2(30)  
STREET       VARCHAR2(100) 
CITY         VARCHAR2(30)  
STATE        VARCHAR2(2)   
ZIP          NUMBER(5)     

The type definition for the new object is:

CREATE TYPE ADDR_T AS OBJECT (  
   street VARCHAR2(100),        
   city VARCHAR2(30),           
   state VARCHAR2(2),           
   zip NUMBER(5, 0) );          

Here are the steps for this redefinition:

1. Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('STEVE','CUSTOMER',
           DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

2. Create the interim table int_customer.

   CREATE TABLE INT_CUSTOMER(
     CID NUMBER,
     NAME  VARCHAR2(30),          
     ADDR  ADDR_T);             
     
   

   Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.

3. Because CUSTOMER is a very large table, specify parallel operations for the next step.

   alter session force parallel dml parallel 4;
   alter session force parallel query parallel 4;
   

4. Start the redefinition process using primary keys.

   BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
      uname       => 'STEVE',
      orig_table  => 'CUSTOMER',
      int_table   => 'INT_CUSTOMER',
      col_mapping => 'cid cid,  name name,
         addr_t(street, city, state, zip) addr');
   END;
   /
   

   Note that addr_t(street, city, state, zip) is a call to the object constructor.

5. Copy dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
      'STEVE','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,
       TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
   END;
   /
   

   Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.

6. Optionally synchronize the interim table.

   BEGIN 
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
   END;
   /
   

7. Complete the redefinition.

   BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
   END;
   /
   

8. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 3

This example addresses the situation where a dependent object must be manually created and registered.

Consider the case where a table T1 has a column named C1, and where this column becomes C2 after the redefinition. Assume that there is an index Index1on C1. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to Index1, and tries to create it on a column C1, which does not exist on the interim table. This results in an error. You must therefore manually create the index on column C2 and register it. Here are the steps:

1. Create the interim table INT_T1 and create an index Int_Index1 on column C2.

2. Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.

3. Register the original (Index1) and interim (Int_Index1) dependent objects.

   BEGIN
   DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
      uname         => 'STEVE',
      orig_table    => 'T1',
      int_table     => 'INT_T1',
      dep_type      => DBMS_REDEFINITION.CONS_INDEX,
      dep_owner     => 'STEVE',
      dep_orig_name => 'Index1',
      dep_int_name  => 'Int_Index1');
   END;
   /
   

4. Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.

5. Optionally synchronize the interim table.

6. Complete the redefinition and drop the interim table.

Example 4

This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. The table containing the partition to be redefined is defined as follows:

CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

The table has a local partitioned index that is defined as follows:

CREATE INDEX sales_index ON salestable 
   (s_saledate, s_productid, s_custid) LOCAL;

Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).

1. Ensure that salestable is a candidate for redefinition.

   BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE(
      uname        => 'STEVE',
      tname        => 'SALESTABLE',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
      part_name    => 'sal03q1');
   END;
   /
   

2. Create the interim table in the TBS_LOW_FREQ tablespace. Because this is a redefinition of a range partition, the interim table is non-partitioned.

   CREATE TABLE int_salestable
   (s_productid NUMBER,
   s_saledate DATE,
   s_custid NUMBER,
   s_totalprice NUMBER)
   TABLESPACE tbs_low_freq;
   

3. Start the redefinition process using rowid.

   BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
      uname        => 'STEVE',
      orig_table   => 'salestable',
      int_table    => 'int_salestable',
      col_mapping  => NULL,
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
      part_name    => 'sal03q1');
   END;
   /
   

4. Manually create any local indexes on the interim table.

   CREATE INDEX int_sales_index ON int_salestable 
   (s_saledate, s_productid, s_custid)
   TABLESPACE tbs_low_freq; 
   

5. Optionally synchronize the interim table.

   BEGIN 
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
      uname      => 'STEVE', 
      orig_table => 'salestable', 
      int_table  => 'int_salestable',
      part_name  => 'sal03q1');
   END;
   /
   

6. Complete the redefinition.

   BEGIN 
   DBMS_REDEFINITION.FINISH_REDEF_TABLE(
      uname      => 'STEVE', 
      orig_table => 'salestable', 
      int_table  => 'int_salestable',
      part_name  => 'sal03q1');
   END;
   /
   

7. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

The following query shows that the oldest partition has been moved to the new tablespace:

select partition_name, tablespace_name from user_tab_partitions
 where table_name = 'SALESTABLE';
 
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        TBS_LOW_FREQ
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS
 
4 rows selected.

Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

• CREATE ANY TABLE

• ALTER ANY TABLE

• DROP ANY TABLE

• LOCK ANY TABLE

• SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

• CREATE ANY TRIGGER

• CREATE ANY INDEX