After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, a large number of DML statements might have been executed on the original table. If you know that this is the case, then it is recommended that you periodically synchronize the interim table with the original table.

When you start an online table redefinition operation with the START_REDEF_TABLE procedure, it creates an internal materialized view to facilitate synchronization. This internal materialized view is refreshed to synchronize the interim table with the original table.

To synchronize the interim table with the original table:

• Run the SYNC_INTERIM_TABLE procedure in the DBMS_REDEFINITION package.

Calling this procedure reduces the time taken by FINISH_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.

To abort the online redefinition process in the event that an error is raised during the redefinition process, or if you choose to terminate the redefinition process manually:

• Run the ABORT_REDEF_TABLE procedure.

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

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

Example 1

This example illustrates online redefinition of a table's storage properties using the REDEF_TABLE procedure.

The original table, named print_ads, is defined in the pm schema as follows:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AD_ID                                              NUMBER(6)
 AD_TEXT                                            CLOB

In this table, the LOB column ad_text uses BasicFiles LOB storage.

An index for the table was created with the following SQL statement:

CREATE INDEX pm.print_ads_ix
    ON print_ads (ad_id)
  TABLESPACE example;

The table is redefined as follows:

• The table is compressed with advanced row compression.

• The table's tablespace is changed from EXAMPLE to NEWTBS. This example assumes that the NEWTBS tablespace exists.

• The index is compressed with COMPRESS 1 compression.

• The index's tablespace is changed from EXAMPLE to NEWIDXTBS. This example assumes that the NEWIDXTBS tablespace exists.

• The LOB column in the table is compressed with COMPRESS HIGH compression.

• The tablespace for the LOB column is changed from EXAMPLE to NEWLOBTBS. This example assumes that the NEWLOBTBS tablespace exists.

• The LOB column is changed to SecureFiles LOB storage.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Run the REDEF_TABLE procedure:

   BEGIN
     DBMS_REDEFINITION.REDEF_TABLE(
       uname                      => 'PM',
       tname                      => 'PRINT_ADS',
       table_compression_type     => 'ROW STORE COMPRESS ADVANCED',
       table_part_tablespace      => 'NEWTBS',
       index_key_compression_type => 'COMPRESS 1',
       index_tablespace           => 'NEWIDXTBS',
       lob_compression_type       => 'COMPRESS HIGH',
       lob_tablespace             => 'NEWLOBTBS',
       lob_store_as               => 'SECUREFILE');
   END;
   / 

Example 2

This example illustrates online redefinition of a table by adding new columns and adding partitioning.

The original table, named emp_redef, is defined in the hr schema as follows:

 Name      Type
 --------- ----------------------------
 EMPNO     NUMBER(5)    <- Primary key
 ENAME     VARCHAR2(15)
 JOB       VARCHAR2(10)
 DEPTNO    NUMBER(3)

The table is redefined as follows:

• New columns mgr, hiredate, sal, and bonus are added.

• The new column bonus is initialized to 0 (zero).

• 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. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. 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(
       uname        => 'hr',
       tname        =>'emp_redef',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table hr.int_emp_redef.

   CREATE TABLE hr.int_emp_redef
           (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(0))
        PARTITION BY RANGE(empno)
          (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
           PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
   

   Ensure that the specified tablespaces exist.

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
       uname        => 'hr', 
       orig_table   => 'emp_redef',
       int_table    => 'int_emp_redef',
       col_mapping  => 'empno empno, ename ename, job job, deptno+10 deptno, 
                        0 bonus',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

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

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'hr', 
       orig_table       => 'emp_redef',
       int_table        => 'int_emp_redef',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => 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 occur. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

6. Query the DBA_REDEFINITION_ERRORS view to check for errors.

   SET LONG  8000
   SET PAGES 8000
   COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
   COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
   COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
    
   SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM 
            DBA_REDEFINITION_ERRORS;
   
   Object Name          Base Table DDL That Caused Error
   -------------------- ---------- ----------------------------------------
   SYS_C006796          EMP_REDEF  CREATE UNIQUE INDEX "HR"."TMP$$_SYS_C006
                                   7960" ON "HR"."INT_EMP_REDEF" ("EMPNO")
                                     PCTFREE 10 INITRANS 2 MAXTRANS 255
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "ADMIN_TBS"
   SYS_C006794          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                   ("ENAME" CONSTRAINT "TMP$$_SYS_C0067940"
                                    NOT NULL ENABLE NOVALIDATE)
   SYS_C006795          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                   ("DEPTNO" CONSTRAINT "TMP$$_SYS_C0067950
                                   " NOT NULL ENABLE NOVALIDATE)
   SYS_C006796          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" ADD CON
                                   STRAINT "TMP$$_SYS_C0067960" PRIMARY KEY
                                    ("EMPNO")
                                     USING INDEX PCTFREE 10 INITRANS 2 MAXT
                                   RANS 255
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "ADMIN_TBS"  ENABLE NOVALID
                                   ATE
   

   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 with COPY_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.

7. (Optional) Synchronize the interim table hr.int_emp_redef.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'hr', 
       orig_table => 'emp_redef', 
       int_table  => 'int_emp_redef');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'hr', 
       orig_table => 'emp_redef', 
       int_table  => 'int_emp_redef');
   END;
   /
   

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

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

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

Example 3

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. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. 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(
       uname        => 'steve',
       tname        =>'customer',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. 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 6, the primary key constraint and index are copied.

4. 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;
   

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

6. Copy dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'steve', 
       orig_table       => 'customer',
       int_table        => 'int_customer',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => FALSE, 
       num_errors       => num_errors,
       copy_statistics  => TRUE);
   END;
   /
   

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

7. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'steve', 
       orig_table => 'customer', 
       int_table  => 'int_customer');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'steve', 
       orig_table => 'customer', 
       int_table  => 'int_customer');
   END;
   /
   

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

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

Example 4

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

The table to be redefined is defined as follows:

CREATE TABLE steve.t1
  (c1 NUMBER);

The table has an index for column c1:

CREATE INDEX steve.index1 ON steve.t1(c1);

Consider the case where column c1 becomes column c2 after the redefinition. 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 in 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 for this redefinition:

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

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

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
      uname        => 'steve',
      tname        => 't1',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
   END;
   /
   

3. Create the interim table int_t1 and create an index int_index1 on column c2.

   CREATE TABLE steve.int_t1
     (c2 NUMBER);
   
   CREATE INDEX steve.int_index1 ON steve.int_t1(c2);
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
       uname        => 'steve', 
       orig_table   => 't1',
       int_table    => 'int_t1',
       col_mapping  => 'c1 c2',
       options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
   END;
   /
   

5. 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;
   /
   

6. Copy the dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'steve', 
       orig_table       => 't1',
       int_table        => 'int_t1',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

7. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'steve', 
       orig_table => 't1', 
       int_table  => 'int_t1');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'steve', 
       orig_table => 't1', 
       int_table  => 'int_t1');
   END;
   /
   

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

Example 5

This example demonstrates redefining multiple partitions. It moves two of the partitions of a range-partitioned sales table to new tablespaces. The table containing the partitions to be redefined is defined as follows:

CREATE TABLE steve.salestable
  (s_productid NUMBER,
  s_saledate DATE,
  s_custid NUMBER,
  s_totalprice NUMBER)
  TABLESPACE users
  PARTITION BY RANGE(s_saledate)
  (PARTITION sal10q1 VALUES LESS THAN (TO_DATE('01-APR-2010', 'DD-MON-YYYY')),
  PARTITION sal10q2 VALUES LESS THAN (TO_DATE('01-JUL-2010', 'DD-MON-YYYY')),
  PARTITION sal10q3 VALUES LESS THAN (TO_DATE('01-OCT-2010', 'DD-MON-YYYY')),
  PARTITION sal10q4 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')));

This example moves the sal10q1 partition to the sales1 tablespace and the sal10q2 partition to the sales2 tablespace. The sal10q3 and sal10q4 partitions are not moved.

To move the partitions, the tablespaces sales1 and sales2 must exist. The following examples create these tablespaces:

CREATE TABLESPACE sales1 DATAFILE '/u02/oracle/data/sales01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
 
CREATE TABLESPACE sales2 DATAFILE '/u02/oracle/data/sales02.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

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

CREATE INDEX steve.sales_index ON steve.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. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. 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    => 'sal10q1, sal10q2');
   END;
   /
   

3. Create the interim tables in the new tablespaces. Because this is a redefinition of a range partition, the interim tables are nonpartitioned.

   CREATE TABLE steve.int_salestb1
     (s_productid NUMBER,
     s_saledate DATE,
     s_custid NUMBER,
     s_totalprice NUMBER)
     TABLESPACE sales1;
   
   CREATE TABLE steve.int_salestb2
     (s_productid NUMBER,
     s_saledate DATE,
     s_custid NUMBER,
     s_totalprice NUMBER)
     TABLESPACE sales2;
   

4. Start the redefinition process using rowid.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
      uname        => 'steve',
      orig_table   => 'salestable',
      int_table    => 'int_salestb1, int_salestb2',
      col_mapping  => NULL,
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

   Notice that the part_name parameter specifies both of the partitions and that the int_table parameter specifies the interim table for each partition. Also, the continue_after_errors parameter is set to TRUE so that the redefinition process continues even if it encounters an error for a particular partition.

5. Manually create any local indexes on the interim tables.

   CREATE INDEX steve.int_sales1_index ON steve.int_salestb1 
   (s_saledate, s_productid, s_custid)
   TABLESPACE sales1; 
   
   CREATE INDEX steve.int_sales2_index ON steve.int_salestb2 
   (s_saledate, s_productid, s_custid)
   TABLESPACE sales2; 
   

6. Optionally synchronize the interim tables.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
      uname      => 'steve', 
      orig_table => 'salestable', 
      int_table  => 'int_salestb1, int_salestb2',
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

7. Complete the redefinition.

   BEGIN 
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
      uname      => 'steve', 
      orig_table => 'salestable', 
      int_table  => 'int_salestb1, int_salestb2',
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

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

9. (Optional) Query the DBA_REDEFINITION_STATUS view to ensure that the redefinition succeeded for each partition.

   SELECT BASE_TABLE_OWNER, BASE_TABLE_NAME, OPERATION, STATUS
     FROM DBA_REDEFINITION_STATUS;
   

   If redefinition failed for any partition, then query the DBA_REDEFINITION_ERRORS view to determine the cause of the failure. Correct the conditions that caused the failure, and rerun online redefinition.

The following query shows that two of the partitions in the table have been moved to the new tablespaces:

SELECT PARTITION_NAME, TABLESPACE_NAME FROM DBA_TAB_PARTITIONS
 WHERE TABLE_NAME = 'SALESTABLE';
 
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL10Q1                        SALES1
SAL10Q2                        SALES2
SAL10Q3                        USERS
SAL10Q4                        USERS
 
4 rows selected.

Example 6

This example illustrates online redefinition of a table with virtual private database (VPD) policies. The example disables all triggers for a table without changing any of the column names or column types in the table.

The table to be redefined is defined as follows:

CREATE TABLE hr.employees( 
   employee_id    NUMBER(6)  PRIMARY KEY, 
   first_name     VARCHAR2(20), 
   last_name      VARCHAR2(25)
	          CONSTRAINT     emp_last_name_nn  NOT NULL, 
   email          VARCHAR2(25)
	          CONSTRAINT     emp_email_nn  NOT NULL, 
   phone_number   VARCHAR2(20), 
   hire_date      DATE
	          CONSTRAINT     emp_hire_date_nn  NOT NULL, 
   job_id         VARCHAR2(10)
	          CONSTRAINT     emp_job_nn  NOT NULL, 
   salary         NUMBER(8,2), 
   commission_pct NUMBER(2,2), 
   manager_id     NUMBER(6), 
   department_id  NUMBER(4),
                  CONSTRAINT     emp_salary_min
                     CHECK (salary > 0), 
                  CONSTRAINT     emp_email_uk
                     UNIQUE (email));

If you installed the HR sample schema, then this table exists in your database.

Assume that the following auth_emp_dep_100 function is created for the VPD policy:

CREATE OR REPLACE FUNCTION hr.auth_emp_dep_100( 
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
 )
 RETURN VARCHAR2
 AS
  return_val VARCHAR2 (400);
  unm        VARCHAR2(30);
 BEGIN
  SELECT USER INTO unm FROM DUAL;
  IF (unm = 'HR') THEN
  return_val := NULL;
 ELSE
  return_val := 'DEPARTMENT_ID = 100';
  END IF;
 RETURN return_val;
END auth_emp_dep_100;
/

The following ADD_POLICY procedure specifies a VPD policy for the original table hr.employees using the auth_emp_dep_100 function:

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'hr',
    object_name      => 'employees',
    policy_name      => 'employees_policy',
    function_schema  => 'hr',
    policy_function  => 'auth_emp_dep_100',
    statement_types  => 'select, insert, update, delete'
   );
 END;
/

In this example, the hr.employees table is redefined to disable all of its triggers. No column names or column types are changed during redefinition. Therefore, specify DBMS_REDEFINITION.CONS_VPD_AUTO for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package" and EXECUTE privilege on the DBMS_RLS package.

   See "Connecting to the Database with SQL*Plus".

2. 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','employees',
         DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table hr.int_employees.

   CREATE TABLE hr.int_employees( 
      employee_id    NUMBER(6), 
      first_name     VARCHAR2(20), 
      last_name      VARCHAR2(25), 
      email          VARCHAR2(25), 
      phone_number   VARCHAR2(20), 
      hire_date      DATE,
      job_id         VARCHAR2(10), 
      salary         NUMBER(8,2), 
      commission_pct NUMBER(2,2), 
      manager_id     NUMBER(6), 
      department_id  NUMBER(4));
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE (
       uname          => 'hr',
       orig_table     => 'employees',
       int_table      => 'int_employees',
       col_mapping    => NULL,
       options_flag   => DBMS_REDEFINITION.CONS_USE_PK,
       orderby_cols   => NULL,
       part_name      => NULL,
       copy_vpd_opt   => DBMS_REDEFINITION.CONS_VPD_AUTO);
   END;
   /
   

   When the copy_vpd_opt parameter is set to DBMS_REDEFINITION.CONS_VPD_AUTO, only the table owner and the user invoking online redefinition can access the interim table during online redefinition.

   Also, notice that the col_mapping parameter is set to NULL. When the copy_vpd_opt parameter is set to DBMS_REDEFINITION.CONS_VPD_AUTO, the col_mapping parameter must be NULL or '*'. See "Handling Virtual Private Database (VPD) Policies During Online Redefinition".

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

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'hr', 
       orig_table       => 'employees',
       int_table        => 'int_employees',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => FALSE, 
       num_errors       => num_errors);
   END;
   /
   

6. Disable all of the triggers on the interim table.

   ALTER TABLE hr.int_employees
     DISABLE ALL TRIGGERS;
   

7. (Optional) Synchronize the interim table hr.int_employees.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'hr', 
       orig_table => 'employees', 
       int_table  => 'int_employees');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'hr', 
       orig_table => 'employees', 
       int_table  => 'int_employees');
   END;
   /
   

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

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

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

Example 7

This example illustrates online redefinition of a table with virtual private database (VPD) policies. The example changes the name of a column in the table.

The table to be redefined is defined as follows:

CREATE TABLE oe.orders( 
   order_id      NUMBER(12)  PRIMARY KEY,
   order_date    TIMESTAMP WITH LOCAL TIME ZONE CONSTRAINT order_date_nn NOT NULL,
   order_mode    VARCHAR2(8),
   customer_id   NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
   order_status  NUMBER(2),
   order_total   NUMBER(8,2),
   sales_rep_id  NUMBER(6),
   promotion_id  NUMBER(6),
   CONSTRAINT    order_mode_lov
                 CHECK (order_mode in ('direct','online')),
   CONSTRAINT    order_total_min
                 check (order_total >= 0));

If you installed the OE sample schema, then this table exists in your database.

Assume that the following auth_orders function is created for the VPD policy:

CREATE OR REPLACE FUNCTION oe.auth_orders( 
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
 )
 RETURN VARCHAR2
 AS
  return_val VARCHAR2 (400);
  unm        VARCHAR2(30);
 BEGIN
  SELECT USER INTO unm FROM DUAL;
  IF (unm = 'OE') THEN
  return_val := NULL;
 ELSE
  return_val := 'SALES_REP_ID = 159';
  END IF;
 RETURN return_val;
END auth_orders;
/

The following ADD_POLICY procedure specifies a VPD policy for the original table oe.orders using the auth_orders function:

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'oe',
    object_name      => 'orders',
    policy_name      => 'orders_policy',
    function_schema  => 'oe',
    policy_function  => 'auth_orders',
    statement_types  => 'select, insert, update, delete');
 END;
/

In this example, the table is redefined to change the sales_rep_id column to sale_pid. When one or more column names or column types change during redefinition, you must specify DBMS_REDEFINITION.CONS_VPD_MANUAL for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package" and EXECUTE privilege on the DBMS_RLS package.

   See "Connecting to the Database with SQL*Plus".

2. 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(
       uname        => 'oe',
       tname        => 'orders',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table oe.int_orders.

   CREATE TABLE oe.int_orders( 
      order_id      NUMBER(12),
      order_date    TIMESTAMP WITH LOCAL TIME ZONE,
      order_mode    VARCHAR2(8),
      customer_id   NUMBER(6),
      order_status  NUMBER(2),
      order_total   NUMBER(8,2),
      sales_pid     NUMBER(6),
      promotion_id  NUMBER(6));
   

   Note that the sales_rep_id column is changed to the sales_pid column in the interim table.

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE (
       uname          => 'oe',
       orig_table     => 'orders',
       int_table      => 'int_orders',
       col_mapping    => 'order_id order_id, order_date order_date, order_mode 
                          order_mode, customer_id customer_id, order_status 
                          order_status, order_total order_total, sales_rep_id 
                          sales_pid, promotion_id promotion_id',
       options_flag   => DBMS_REDEFINITION.CONS_USE_PK,
       orderby_cols   => NULL,
       part_name      => NULL,
       copy_vpd_opt   => DBMS_REDEFINITION.CONS_VPD_MANUAL);
   END;
   /
   

   Because a column name is different in the original table and the interim table, DBMS_REDEFINITION.CONS_VPD_MANUAL must be specified for the copy_vpd_opt parameter. See "Handling Virtual Private Database (VPD) Policies During Online Redefinition".

5. Create the VPD policy on the interim table.

   In this example, complete the following steps:

   1. Create a new function called auth_orders_sales_pid for the VPD policy that specifies the sales_pid column instead of the sales_rep_id column:

      CREATE OR REPLACE FUNCTION oe.auth_orders_sales_pid( 
        schema_var IN VARCHAR2,
        table_var IN VARCHAR2
       )
       RETURN VARCHAR2
       AS
        return_val VARCHAR2 (400);
        unm        VARCHAR2(30);
       BEGIN
        SELECT USER INTO unm FROM DUAL;
        IF (unm = 'OE') THEN
        return_val := NULL;
       ELSE
        return_val := 'SALES_PID = 159';
        END IF;
       RETURN return_val;
      END auth_orders_sales_pid;
      /
      

   2. Run the ADD_POLICY procedure and specify the new function auth_orders_sales_pid and the interim table int_orders:

      BEGIN
        DBMS_RLS.ADD_POLICY (
          object_schema    => 'oe',
          object_name      => 'int_orders',
          policy_name      => 'orders_policy',
          function_schema  => 'oe',
          policy_function  => 'auth_orders_sales_pid',
          statement_types  => 'select, insert, update, delete');
       END;
      /
      

6. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on oe.int_orders.)

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'oe', 
       orig_table       => 'orders',
       int_table        => 'int_orders',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

   Note that the ignore_errors argument is set to TRUE for this call. The reason is that the original table has an index and a constraint related to the sales_rep_id column, and this column is changed to sales_pid in the interim table. The next step shows the errors and describes how to create the index and the constraint on the interim table.

7. Query the DBA_REDEFINITION_ERRORS view to check for errors.

   SET LONG  8000
   SET PAGES 8000
   COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
   COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
   COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
    
   SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM 
            DBA_REDEFINITION_ERRORS;
   
   Object Name          Base Table DDL That Caused Error
   -------------------- ---------- ----------------------------------------
   ORDERS_SALES_REP_FK  ORDERS     ALTER TABLE "OE"."INT_ORDERS" ADD CONSTR
                                   AINT "TMP$$_ORDERS_SALES_REP_FK1" FOREIG
                                   N KEY ("SALES_REP_ID")
                                             REFERENCES "HR"."EMPLOYEES" 
                                   ("EMPLOYE
                                   E_ID") ON DELETE SET NULL DISABLE
   ORD_SALES_REP_IX     ORDERS     CREATE INDEX "OE"."TMP$$_ORD_SALES_REP_I
                                   X0" ON "OE"."INT_ORDERS" ("SALES_REP_ID"
                                   )
                                     PCTFREE 10 INITRANS 2 MAXTRANS 255 COM
                                   PUTE STATISTICS
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "EXAMPLE"
   TMP$$_ORDERS_SALES_R ORDERS     ALTER TABLE "OE"."INT_ORDERS" ADD CONSTR
   EP_FK0                          AINT "TMP$$_TMP$$_ORDERS_SALES_RE0" FORE
                                   IGN KEY ("SALES_REP_ID")
                                             REFERENCES "HR"."INT_EMPLOYEES" 
                                   ("EMP
                                   LOYEE_ID") ON DELETE SET NULL DISABLE
   

   If necessary, correct the errors reported in the output.

   In this example, original table has an index and a foreign key constraint on the sales_rep_id column. The index and the constraint could not be copied to the interim table because the name of the column changed from sales_rep_id to sales_pid.

   To correct the problems, add the index and the constraint on the interim table by completing the following steps:

   1. Add the index:

      ALTER TABLE oe.int_orders 
        ADD (CONSTRAINT orders_sales_pid_fk 
            FOREIGN KEY (sales_pid) 
            REFERENCES hr.employees(employee_id)
            ON DELETE SET NULL);
      

   2. Add the foreign key constraint:

      CREATE INDEX ord_sales_pid_ix ON oe.int_orders (sales_pid);
      

8. (Optional) Synchronize the interim table oe.int_orders.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'oe', 
       orig_table => 'orders', 
       int_table  => 'int_orders');
   END;
   /
   

9. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'oe', 
       orig_table => 'orders', 
       int_table  => 'int_orders');
   END;
   /
   

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

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

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

Example 8

This example illustrates making multiple changes to a table using online redefinition.

The table to be redefined is defined as follows:

CREATE TABLE testredef.original( 
   col1 NUMBER PRIMARY KEY,
   col2 VARCHAR2(10),
   col3 CLOB,
   col4 DATE)
ORGANIZATION INDEX;

The table is redefined as follows:

• The table is compressed with advanced row compression.

• The LOB column is changed to SecureFiles LOB storage.

• The table's tablespace is changed from example to testredeftbs, and the table's block size is changed from 8KB to 16KB.

  This example assumes that the database block size is 8KB. This example also assumes that the DB_16K_CACHE_SIZE initialization parameter is set and that the testredef tablespace was created with a 16KB block size. For example:

  CREATE TABLESPACE testredeftbs  
    DATAFILE '/u01/app/oracle/oradata/testredef01.dbf' SIZE 500M   EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
    SEGMENT SPACE MANAGEMENT AUTO 
    BLOCKSIZE 16384;
  

• The table is partitioned on the col1 column.

• The col5 column is added.

• The col2 column is dropped.

• Columns col3 and col4 are renamed, and their position in the table is changed.

• The type of the col3 column is changed from DATE to TIMESTAMP.

• The table is changed from an index-organized table (IOT) to a heap-organized table.

• The table is defragmented.

  To demonstrate defragmentation, the table must be populated. For the purposes of this example, you can use this PL/SQL block to populate the table:

  DECLARE
    V_CLOB CLOB;
  BEGIN
     FOR I IN 0..999 LOOP
        V_CLOB := NULL;
        FOR J IN 1..1000 LOOP
           V_CLOB := V_CLOB||TO_CHAR(I,'0000');
        END LOOP;
        INSERT INTO testredef.original VALUES(I,TO_CHAR(I),V_CLOB,SYSDATE+I);
        COMMIT;
     END LOOP;
     COMMIT;
  END;
  /
  

  Run the following SQL statement to fragment the table by deleting every third row:

  DELETE FROM testredef.original WHERE (COL1/3) <> TRUNC(COL1/3);
  

  You can confirm the fragmentation by using the DBMS_SPACE.SPACE_USAGE procedure.

  See Also:

  Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SPACE.SPACE_USAGE procedure

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. 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(
       uname        => 'testredef',
       tname        => 'original',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table testredef.interim.

   CREATE TABLE testredef.interim( 
       col1 NUMBER,
       col3 TIMESTAMP,
       col4 CLOB,
       col5 VARCHAR2(3))
       LOB(col4) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING)
       PARTITION BY RANGE (COL1) (
          PARTITION par1 VALUES LESS THAN (333),
          PARTITION par2 VALUES LESS THAN (666),
          PARTITION par3 VALUES LESS THAN (MAXVALUE))
      TABLESPACE testredeftbs
      ROW STORE COMPRESS ADVANCED;
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
        uname        => 'testredef',
        orig_table   => 'original',
        int_table    => 'interim',
        col_mapping  => 'col1 col1, TO_TIMESTAMP(col4) col3, col3 col4',
        options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

5. Copy the dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'testredef', 
       orig_table       => 'original',
       int_table        => 'interim',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

6. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname       => 'testredef', 
       orig_table  => 'original',
       int_table   => 'interim');
   END;
   /
   

7. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname       => 'testredef', 
       orig_table  => 'original',
       int_table   => 'interim');
   END;
   /