dbms_redefinition表的重定义

转载： 原文链接http://oracle.chinaitlab.com/induction/801274.html

DBMS_REDEFINITION.CAN_REDEF_TABLE检查表是否可以重定义

　　其中EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CSADMIN', 'XXOLD', DBMS_REDEFINITION.CONS_USE_PK);代表用主键，是默认选项

　　EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CSADMIN', 'XXNEW', DBMS_REDEFINITION.CONS_USE_rowid);代表用rowid

　　用rowid的方式在转换之后会生成一个伪列，查询伪列名称

　　select * from user_tab_cols where table_name='OLD'

　　然后将伪列drop掉

　　alter table old set unused column "SYS_C00003_09111110:43:01$"

　　alter table old drop unused column

　　查询unused column 通过：select * from user_unused_col_tabs

　　在执行EXEC DBMS_REDEFINITION.START_REDEF_TABLE('CSADMIN', 'XXOLD','XXNEW',2);之后系统会将旧表的数据刷新到新表，同时生成一张名为

　　M$LOG_XXOLD的物化视图。此后，对新表的更新都会记录在这个视图里，在执行DBMS_REDEFINITION.sync_interim_table('CSADMIN','XXOLD','XXNEW');

　　或者EXEC DBMS_REDEFINITION.finish_redef_table('CSADMIN','XXOLD','XXNEW');后会自动将物化视图的记录更新到新表中。

　　sync_interim_table的目的是为了缩短finish时锁定表的时间。在执行finish之后，会自动删除这个物化视图。

　　如果中间过程出错要重新定义表的话，先执行 dbms_redefinition.abort_redef_table中断转换，然后要手工删除物化视图和新表再重新定义。

　　DROP MATERILIZED VIEW XXNEW;

　　DROP TABLE XXNEW;

　　DROP MATERILIZED VIEW LOG ON XXOLD;

　　交换后，旧表上的index和constraint也会交换到新表，同样新表上的定义也会交换给旧表。所以在finish前要先在新表上做相应的index和constraint，还有表的权限定义

1--验证是否可以执行
SQL> execute dbms_redefinition.can_redef_table ('username','usertable_old' );        ---  第三个参数可以指定用主键。rowid
     execute dbms_redefinition.can_redef_table ('username','usertable_old' ,DBMS_REDEFINITION.CONS_USE_PK); ---代表用主键，是默认选项
   execute dbms_redefinition.can_redef_table ('username','usertable_old' ,DBMS_REDEFINITION.CONS_USE_rowid); --代表用rowid

　测试1：不管新表中的内容如何，在start之后都会被刷新成旧表的内容

exec dbms_redefinition.start_redef_table('csadmin','old','new',null,2);

 

  　测试2：在start之后，对新表的更新会记录在mlog$_old中,执行sync_interim_table时同步到new，同时清空

　　mlog$_old;在finish时也会同步一次，同时删除mlog$_old;

exec dbms_redefinition.start_redef_table('csadmin','old','new',null,2);

exec dbms_redefinition.sync_interim_table('csadmin','old','new');

exec dbms_redefinition.finish_redef_table('csadmin','old','new');

测试3：用DBMS_REDEFINITION调整列的位置

exec dbms_redefinition.start_redef_table('CSADMIN','OLD','NEW','USERNAME USERNAME,LSTTIME LSTTIME',2);

exec dbms_redefinition.finish_redef_table('csadmin','old','new');

测试4：转换原表为分区表

　　Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

　　Connected as csadmin

　　SQL> drop table xxnew;

　　drop table xxnew

　　ORA-12083: must use DROP MATERIALIZED VIEW to drop "CSADMIN"."XX_NEW"

　　SQL> drop materialized view xx_new;

　　Materialized view dropped

　　SQL> drop table xx_new;

　　Table dropped

　　SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CSADMIN', 'XXOLD', DBMS_REDEFINITION.cons_use_rowid);

　　begin DBMS_REDEFINITION.CAN_REDEF_TABLE('CSADMIN', 'XXOLD', DBMS_REDEFINITION.cons_use_rowid); end;

　　ORA-12091: cannot online redefine table "CSADMIN"."XXOLD" with materialized views

　　ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

　　ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478

　　ORA-06512: at line 2

　　SQL> select * from user_mview_logs where master='XXOLD';

　　LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER ROWIDS PRIMARY_KEY OBJECT_ID FILTER_COLUMNS SEQUENCE INCLUDE_NEW_VALUES

　　------------------------------ ------------------------------ ------------------------------ ------------------------------ ------ ----------- --------- -------------- -------- ------------------

　　CSADMIN XXOLD MLOG$_XXOLD NO YES NO NO NO NO

　　SQL> drop materialized view XXOLD;

　　ORA-12003: materialized view "CSADMIN"."XXOLD" does not exist

　　SQL> drop materialized view LOG ON XXOLD;

　　Materialized view log dropped

　　SQL> select * from user_mview_logs where master='XXOLD';

　　LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER ROWIDS PRIMARY_KEY OBJECT_ID FILTER_COLUMNS SEQUENCE INCLUDE_NEW_VALUES

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

转载于:http://blog.itpub.net/21634752/viewspace-702559/