dbms_redefinition表的重定义

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：不管新表中的内容如何，在start之后都会被刷新成旧表的内容
SQL> select count(*) from old;

COUNT(*)
----------
4

SQL> select count(*) from new;

COUNT(*)
----------
4
SQL> insert into new values ('e',sysdate);

1 row inserted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

SQL> select count(*) from new;

COUNT(*)
----------
4

SQL> select count(*) from old;

COUNT(*)
----------
4

****************************************************************************************
测试2：在start之后，对新表的更新会记录在mlog$_old中,执行sync_interim_table时同步到new，同时清空
mlog$_old;在finish时也会同步一次，同时删除mlog$_old;
SQL> exec dbms_redefinition.start_redef_table('csadmin','old','new',null,2);

PL/SQL procedure successfully completed

SQL> select count(*) from new;

COUNT(*)
----------
4

SQL> select count(*) from old;

COUNT(*)
----------
4

SQL> insert into old values ('e',sysdate);

1 row inserted

SQL> commit;

Commit complete

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

COUNT(*)
----------
1

SQL> select count(*) from new;

COUNT(*)
----------
4

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

PL/SQL procedure successfully completed

SQL> select count(*) from old;

COUNT(*)
----------
5

SQL> select count(*) from new;

COUNT(*)
----------
5

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

COUNT(*)
----------
0

SQL> insert into old values ('f',sysdate);

1 row inserted

SQL> commit;

Commit complete

SQL> select count(*) from old;

COUNT(*)
----------
6

SQL> select count(*) from new;

COUNT(*)
----------
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_VALUES
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------ ----------- --------- -------------- -------- ------------------

SQL> SELECT COUNT(*) FROM XXOLD;

COUNT(*)
----------
2880844

SQL> SELECT COUNT(*) FROM XXNEW;

COUNT(*)
----------
0

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
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------ ----------- --------- -------------- -------- ------------------

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

LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER ROWIDS PRIMARY_KEY OBJECT_ID FILTER_COLUMNS SEQUENCE INCLUDE_NEW_VALUES
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------ ----------- --------- -------------- -------- ------------------

SQL> CREATE TABLE XXNEW partition by range(COMMANDFINISHTIME,COMMANDLSTTXDATE)
2 (
3 partition PT_XXOLD_2004 values less than (TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD
4
5 HH24:MI:SS'),TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
6 tablespace USER_DATA_2004
7 ,
8 partition PT_XXOLD_2005 values less than (TO_DATE('2006-01-01 00:00:00', 'YYYY-MM-DD
9
10 HH24:MI:SS'),TO_DATE('2006-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
11 tablespace USER_DATA_2005
12 ,
13 partition PT_XXOLD_2006 values less than (TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD
14
15 HH24:MI:SS'),TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
16 tablespace USER_DATA_2006
17 ,
18 partition PT_XXOLD_2007 values less than (TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD
19
20 HH24:MI:SS'),TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
21 tablespace USER_DATA_2007
22 ,
23 partition PT_XXOLD_2008 values less than (TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD
24
25 HH24:MI:SS'),TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
26 tablespace USER_DATA_2008
27 ,
28 partition PT_XXOLD_2009 values less than (TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD
29
30 HH24:MI:SS'),TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
31 tablespace USER_DATA_2009
32 ,
33 partition PT_XXOLD_2010 values less than (MAXVALUE,MAXVALUE)
34 tablespace USER_DATA_2010
35 )
36 AS SELECT * FROM XXOLD WHERE 1=0;

CREATE TABLE XXNEW partition by range(COMMANDFINISHTIME,COMMANDLSTTXDATE)
(
partition PT_XXOLD_2004 values less than (TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2004
,
partition PT_XXOLD_2005 values less than (TO_DATE('2006-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2006-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2005
,
partition PT_XXOLD_2006 values less than (TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2006
,
partition PT_XXOLD_2007 values less than (TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2007
,
partition PT_XXOLD_2008 values less than (TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2008
,
partition PT_XXOLD_2009 values less than (TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD

HH24:MI:SS'),TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace USER_DATA_2009
,
partition PT_XXOLD_2010 values less than (MAXVALUE,MAXVALUE)
tablespace USER_DATA_2010
)
AS SELECT * FROM XXOLD WHERE 1=0


SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('CSADMIN', 'XXOLD', 'XXNEW',null,2);

PL/SQL procedure successfully completed

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 YES NO NO NO NO NO

SQL> desc MLOG$_XXOLD
Name Type Nullable Default Comments
--------------- ------------- -------- ------- --------
M_ROW$$ VARCHAR2(255) Y
SNAPTIME$$ DATE Y
DMLTYPE$$ VARCHAR2(1) Y
OLD_NEW$$ VARCHAR2(1) Y
CHANGE_VECTOR$$ RAW(255) Y
MLOG$_XXOLD
SQL> select count(*) from MLOG$_XXOLD;

COUNT(*)
----------
0

SQL>
SQL> create index XXIDX_CMDFINISH_NEW on XXNEW(COMMANDFINISHTIME)
2 tablespace USER_IDX_PT
3 pctfree 10
4 initrans 2
5 maxtrans 255
6 storage
7 (
8 initial 64K
9 minextents 1
10 maxextents unlimited
11 );

Index created

SQL> select * from user_indexes u where u.table_name
2 ='XXNEW';

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
I_SNAP$_XXOLD_N NORMAL CSADMIN XXNEW TABLE UNIQUE DISABLED USER_DATA 2 255 65536 1 2147483645 10 YES 2 13276 2880844 1 1 141481 VALID 2880844 2880844 2009-11-10 11 1 1 NO N N N DEFAULT NO NO NO NO NO
XXIDX_CMDFINISH_NEW NORMAL CSADMIN XXNEW TABLE NONUNIQUE DISABLED USER_IDX_PT 2 255 65536 1 2147483645 10 YES 2 9233 2149054 1 1 2331831 VALID 2880608 2880608 2009-11-10 13 1 1 NO N N N DEFAULT NO NO NO NO NO

1 rows selected

SQL> grant select on XXNEW to PUBLIC;

Grant succeeded
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('CSADMIN', 'XXOLD', 'XXNEW');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) FROM XXOLD;

COUNT(*)
----------
2880847

SQL> SELECT COUNT(*) FROM MLOG$_XXOLD;

SELECT COUNT(*) FROM MLOG$_XXOLD

ORA-00942: table or view does not exist

SQL> SELECT COUNT(*) FROM XXNEW;

COUNT(*)
----------
2880847

SQL> alter table XXOLD
2 add constraint XXOLD_KEY primary key (COMMANDSEQ)
3 using index
4 tablespace USER_DATA
5 pctfree 10
6 initrans 2
7 maxtrans 255
8 storage
9 (
10 initial 64K
11 minextents 1
12 maxextents unlimited
13 );

Table altered

**********************************************************************
例外，在start之后，向原表插入数据出现了ora-600错误。据说是10g的bug，待证实
http://blog.csdn.net/newzqs/archive/2009/06/19/4281926.aspx
SQL> CREATE TABLE XXTMP AS SELECT * FROM XXOLD WHERE 1=0;

Table created

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('CSADMIN', 'XXOLD','XXTMP',NULL,2);

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) FROM XXOLD;

COUNT(*)
----------
0

SQL> SELECT COUNT(*) FROM XXNEW;

COUNT(*)
----------
4

SQL> SELECT COUNT(*) FROM XXTMP;

COUNT(*)
----------
0

SQL> INSERT INTO xxold select * from xxnew;

INSERT INTO xxold select * from xxnew

ORA-00600: internal error code, arguments: [kghGetHpSz1], [0x4AA2BF24], [], [], [], [], [], []

SQL> INSERT INTO xxnew select * from xxnew;

4 rows inserted

SQL> INSERT INTO xxold select * from xxnew;

INSERT INTO xxold select * from xxnew

ORA-00600: internal error code, arguments: [kghGetHpSz1], [0x49505F94], [], [], [], [], [], []

SQL> INSERT INTO xxnew values('a',sysdate);

1 row inserted


SQL> commit
2 ;

Commit complete

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