oracle在线重定义包DBMS_REDIFINITION #

--原表的定义语句, 做在线重定义之前, 记得先把原表定义语句先取出来备用, 可以从toad里取得.
ALTER TABLE U_TEST.CARD_TEST DROP PRIMARY KEY CASCADE;
DROP TABLE U_TEST.CARD_TEST CASCADE CONSTRAINTS;
CREATE TABLE U_TEST.CARD_TEST
(
CARD_NO       VARCHAR2(32 BYTE)               NOT NULL,
OPERATE_TIME DATE,
OPERATOR_ID   NUMBER(10)                      DEFAULT 0                     NOT NULL
)
TABLESPACE U_TEST_USER01;
CREATE UNIQUE INDEX U_TEST.CARD_TEST_PK ON U_TEST.CARD_TEST (CARD_NO) TABLESPACE U_TEST_USER01;
DROP SYNONYM U_TEST_APP.CARD_TEST;
CREATE SYNONYM U_TEST_APP.CARD_TEST FOR U_TEST.CARD_TEST;
ALTER TABLE U_TEST.CARD_TEST ADD CONSTRAINT CARD_TEST_PK PRIMARY KEY (CARD_NO) USING INDEX TABLESPACE U_TEST_USER01;
GRANT SELECT ON U_TEST.CARD_TEST TO U_TEST_APP;

--做一个临时存储过程,模拟不断在对该表插入数据,因为在线重定义期间要求不影响原有DML操作
grant execute on SYS.DBMS_LOCK to U_TEST;
CREATE OR REPLACE PROCEDURE U_TEST.sp_test
AS
   v_temp   pls_integer := 0;
BEGIN
   FOR i IN 1 .. 1200
   LOOP
      --注意insert语句里要有字段列表, 否则重定义期间该过程会有ORA-00947: not enough values错误
      INSERT INTO U_TEST.CARD_TEST (CARD_NO, OPERATE_TIME, OPERATOR_ID)
        VALUES   ('tmp' || TO_CHAR (i), SYSDATE, i);

      v_temp := v_temp + 1;
      COMMIT;
      sys.DBMS_LOCK.sleep (1 / 2);
   END LOOP;
END;
/

--下面开始重定义, 以dba用户登陆并开始执行临时存储过程
SQL> conn system/xxx
SQL> exec U_TEST.sp_test;

-- 联机重定义必须考虑的问题: 会占用源表两倍的空间, 此外还要考虑物化视图Log的空间以及带来的其他开销. 参考http://orafaq.com/node/4

-- 0. 确认正在往表里插入数据
SQL> select count(*) from U_TEST.CARD_TEST;

-- 1. 确认是否可重定义,如果表不满足重定义的条件, 将会报错并给出原因, 默认要求原表有主键, 否则得使用rowid标志
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('U_TEST', 'CARD_TEST');

-- 2. 建立一个空的中间表,中间表里定义好新增字段属性
SQL> CREATE TABLE U_TEST.CARD_TEST_TMP
(
CARD_NO       VARCHAR2(32 BYTE) NOT NULL,
OPERATE_TIME DATE,
OPERATOR_ID   NUMBER(10) DEFAULT 0 NOT NULL ,
GAME_ID       NUMBER(3) DEFAULT 1 NOT NULL
)
TABLESPACE U_TEST_USER01;

-- 3. 开始重定义
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 此时也可以做修改列名的操作,如:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP', 'CARD_NO CARD_NO, OPERATE_TIME OPERATE_DATE, OPERATOR_ID+100 OPERATOR_ID');

-- 4. 在中间表上建立原表就有的索引,约束,授权,触发器语句等.
-- 在10g中, 如果这些定义变化了可以通过REGISTER_DEPENDENT_OBJECT()来创建, 否则调用COPY_TABLE_DEPENDENTS()即可.

-- 4.1 定义发生变化的情况下(比如修改了主键)
SQL> ALTER TABLE U_TEST.CARD_TEST_TMP ADD CONSTRAINT CARD_TEST_PK1 PRIMARY KEY (CARD_NO, GAME_ID) USING INDEX TABLESPACE U_TEST_index01;
SQL> exec DBMS_REDEFINITION.register_dependent_object('U_TEST','CARD_TEST','CARD_TEST_TMP',
    dep_type => DBMS_REDEFINITION.cons_constraint,
    dep_owner => 'U_TEST',
    dep_orig_name => 'CARD_TEST_PK',
    dep_int_name => 'CARD_TEST_PK1');
                                     
--4.2 定义未发生变化的情况下(默认把所有索引,约束,授权,触发器语句都拷贝过来,不包含同义词,同义词会一直保留于原表)
DECLARE
   retval   NUMBER (5);
BEGIN
   DBMS_REDEFINITION.copy_table_dependents ('U_TEST',
                                            'CARD_TEST',
                                            'CARD_TEST_TMP',
                                            ignore_errors   => TRUE,
                                            num_errors      => retval);
   DBMS_OUTPUT.put_line (retval);
END;
/

-- 5. 重定义期间, 原表数据可能发生了变化, 选择执行一次或多次的SYNC_INTERIM_TABLE()对数据同步, 以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间.
SQL> EXEC dbms_redefinition.sync_interim_table('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');

-- 6. 完成表的重定义, 原始表会被独占模式锁定一小段时间, 具体时间和表的数据量有关.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 此时如果重定义失败, 通过ABORT_REDEF_TABLE()过程释放快照, 然后重来
SQL> EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');

-- 7. 验证充定义结果
SQL> desc U_TEST.CARD_TEST;
Name                        Null?    Type
--------------------------- -------- --------------------
CARD_NO                     NOT NULL VARCHAR2(32)
OPERATE_TIME                         DATE
OPERATOR_ID                 NOT NULL NUMBER(10)
GAME_ID                     NOT NULL NUMBER(3)
-- 确认还正常在往表里插入数据
SQL> select count(*) from U_TEST.CARD_TEST;

-- 8. 删除中间表并编译所有可能失效对象
SQL> drop TABLE U_TEST.CARD_TEST_TMP;
SQL> exec utl_recomp.recomp_serial();

--END--