5

　　SQL> select count(*) from mlog$_old;

　　COUNT(*)

　　----------

　　1

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

　　PL/SQL procedure successfully completed

　　SQL> select count(*) from old;

　　COUNT(*)

　　----------

　　6

　　SQL> select count(*) from new;

　　COUNT(*)

　　----------

　　6

　　SQL> select count(*) from mlog$_old;

　　select count(*) from mlog$_old

　　ORA-00942: table or view does not exist

　　*********************************************

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

　　SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='OLD';

　　INDEX_NAME

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

　　IDX_USERNAME

　　SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='OLD';

　　CONSTRAINT_NAME

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

　　USERNAME_KEY

　　SQL> TRUNCATE TABLE NEW;

　　Table truncated

　　SQL> DESC OLD;

　　Name Type Nullable Default Comments

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

　　LSTTIME DATE Y

　　USERNAME VARCHAR2(10)

　　SQL> DESC NEW

　　Name Type Nullable Default Comments

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

　　USERNAME VARCHAR2(10) Y

　　LSTTIME DATE Y

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

　　PL/SQL procedure successfully completed

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

　　PL/SQL procedure successfully completed

　　SQL> DESC OLD;

　　Name Type Nullable Default Comments

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

　　USERNAME VARCHAR2(10) Y

　　LSTTIME DATE Y

　　SQL> DESC NEW

　　Name Type Nullable Default Comments

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

　　LSTTIME DATE Y

　　USERNAME VARCHAR2(10)

　　SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='OLD';

　　INDEX_NAME

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

　　SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='OLD';

　　CONSTRAINT_NAME

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

　　SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='NEW';

　　INDEX_NAME

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

　　IDX_USERNAME

　　SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='NEW';

　　CONSTRAINT_NAME

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

　　USERNAME_KEY

　　测试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_VALUES5