DBMS_REDEFINITION

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

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.

1. 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.
2. 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.
3. Optionally, synchronize the interim table hr.int_admin_emp.

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

4. 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.
5. Wait for any long-running queries against the interim table to complete, and then drop the interim table.