使用Oracle在线重定义包 DBMS_REDEFINITION 在不停业务的情况下增加或修改字段
2011-06-09 14:02 Tracy. 阅读(990) 评论(0) 编辑 收藏 举报 --原表的定义语句, 做在线重定义之前, 记得先把原表定义语句先取出来备用, 可以从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-- |
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2011/06/09/2076343.html