表的在线重定义

SQL> CREATE TABLE scott.admin_emp
         (empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         job        VARCHAR2(10),
         mgr        NUMBER(5),

 deptno     NUMBER(3) NOT NULL);

Table created.

SQL> insert into scott.admin_emp select empno,ename,job,mgr,deptno from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

 

⊙ 创建一个中间表

SQL> CREATE TABLE scott.int_admin_emp
          (empno      NUMBER(5) PRIMARY KEY,
           ename      VARCHAR2(15) NOT NULL,
           job        VARCHAR2(10),
           mgr        NUMBER(5),
           deptno     NUMBER(3) NOT NULL,
           hiredate   DATE DEFAULT (sysdate),
           sal        NUMBER(7,2),
           bonus      NUMBER (7,2) DEFAULT(1000))  ;

Table created.

 

⊙ 通过DBMS_REDEFINITION.CAN_REDEF_TABLE验证是否能通过上面两种方法在线重定义表

SQL> BEGIN
              DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','admin_emp',DBMS_REDEFINITION.CONS_USE_PK);
    END;
  /

PL/SQL procedure successfully completed.

 

⊙ 开始DBMS_REDEFINITION.START_REDEF_TABLE重定义表 

SQL> BEGIN  DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'admin_emp','int_admin_emp','empno empno, ename ename, job job,mgr mgr,sysdate hiredate,0 sal,deptno+10 deptno,0 bonus',dbms_redefinition.cons_use_pk);END;
  2  /

PL/SQL procedure successfully completed.

 

⊙ 重组织后，通过DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS拷贝改表相关对象（如索引、触发器等）到中间表

SQL> DECLARE num_errors PLS_INTEGER;
       BEGIN
       DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott', 'admin_emp','int_admin_emp',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
   END;
    /

PL/SQL procedure successfully completed.

 

SQL> BEGIN  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 'admin_emp', 'int_admin_emp');END;   /

PL/SQL procedure successfully completed

 

⊙ 执行DBMS_REDEFINITION.FINISH_REDEF_TABLE完成这次定义,这里FINISH_REDEF_TABLE会等待所有的DML事务结束

SQL> BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'admin_emp', 'int_admin_emp');END; /

PL/SQL procedure successfully completed

 

SQL> desc scott.admin_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(5)
 ENAME                                     NOT NULL VARCHAR2(15)
 JOB                                                VARCHAR2(10)
 MGR                                                NUMBER(5)
 DEPTNO                                    NOT NULL NUMBER(3)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 BONUS                                              NUMBER(7,2)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668637/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668637/