http://blog.itpub.net/post/468/12855
在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。
在线重定义表具有以下功能：
1、修改表的存储参数；
2、可以将表转移到其他表空间；
3、增加并行查询选项；
4、增加或删除分区；
5、重建表以减少碎片；
6、将堆表改为索引组织表或相反的操作；
7、增加或删除一个列。
调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
在线重定义表的步骤如下：
1.选择一种重定义方法：
存在两种重定义方法：一种是基于主键；另一种是基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。
2.调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程，如果表不满足重定义的条件，将会报错并给出原因。
3.在用一个方案中建立一个空的中间表，根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等。
4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程，并提供下列参数：被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。
如果映射方法没有提供，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列。如果没有给出重定义方法，则认为使用主键方式。
5.在中间表上建立触发器、索引和约束，并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。
当重定义完成时，中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。
6.（可选）如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。
7.执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关。
执行完FINISH_REDEF_TABLE()过程后，原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。
8.（可选）可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
下面是进行重定义操作后的结果：
原始表根据中间表的属性和特性进行重定义；
START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权，现在定义在原始表上。中间表上disabled的约束在原始表上处于enabled状态。
原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。
任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。
如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
http://hi.baidu.com/edeed/blog/item/ee6811dfb7e5d61b63279832.html
|
--原表的定义语句, 做在线重定义之前, 记得先把原表定义语句先取出来备用, 可以从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-- |
