redefination table online exmaple for oracle manual

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 onhr.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 tablehr.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.