SQL> CREATE TABLE T(ID NUMBER ,TIME DATE);
Table created.
SQL> INSERT INTO T SELECT ROWNUM,CREATED FROM ALL_OBJECTS;
13606 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
13606
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('XYS','T', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('XYS','T', DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "XYS"."T" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1
SQL> alter table t add constraint pk_t primary key(id);
Table altered.
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('XYS','T', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL> select to_char(min(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MIN(TIME),'
-------------------
2014-10-10 11:46:27
SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MAX(TIME),'
-------------------
2015-09-28 10:35:34
SQL> CREATE TABLE T_NEW(
2 ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
3 (
4 PARTITION T_2013 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))，
5 PARTITION T_2014 VALUES LESS THAN (TO_DATE('2015-1-1', 'YYYY-MM-DD'))
6 );
Table created.
SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1
上面的提示很显然是分区表建立时有值在分区表之外，未被包含，可以查询下数据把缺失的分区表建立起来
SQL> ALTER TABLE T_NEW ADD PARTITION T_2015 VALUES LESS THAN (TO_DATE('2016-1-1', 'YYYY-MM-DD'))
2 ;
Table altered.
SQL>
SQL> select to_char(min(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MIN(TIME),'
-------------------
2014-10-10 11:46:27
SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MAX(TIME),'
-------------------
2015-09-28 10:35:34
SQL> select partition_name from user_tab_partitions where table_name='T_NEW';
PARTITION_NAME
------------------------------
T_2013
T_2014
T_2015
SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;
*
ERROR at line 1:
ORA-23539: table "XYS"."T" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1
SQL> select mview_name from user_mviews;
MVIEW_NAME
------------------------------
T_NEW
SQL> drop materialized view log on T;
Materialized view log dropped.
SQL> select mview_name from user_mviews;
no rows selected
--当执行start_redef_table的时候提示：currently being redefined，
这个时候需要做2个动作：
1.drop和表有关的实体化试图，因为在做start_redef_table的时候会生成一个实体化试图。
2.在下面说明了***
SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;
*
ERROR at line 1:
ORA-23539: table "XYS"."T" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1
SQL> exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.sync_interim_table('XYS','T','T_NEW'); END;
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 119
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1752
ORA-06512: at line 1
SQL> select count(*) from t;
COUNT(*)
----------
13606
SQL> select count(*) from t_new;
COUNT(*)
----------
0
第二个
***如果drop了实体化试图之后还提示错误(ORA-23539: table "XYS"."T" currently being redefined)，那么执行一下：abort_redef_table
之后再次执行start_redef_table.
SQL> exec DBMS_REDEFINITION.abort_redef_table('xys', 't', 't_new')
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
13606
SQL> select count(*) from t_new;
COUNT(*)
----------
13606
SQL> select * from t where rownum<=2;
ID TIME
---------- -------------------
1288 2014/10/10 11:46:50
1289 2014/10/10 11:46:50
SQL> insert into t values(100000,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
13607
SQL> select count(*) from t_new;
COUNT(*)
----------
13606
SQL> exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');
PL/SQL procedure successfully completed.
SQL> select count(*) from t_new;
COUNT(*)
----------
13607
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('XYS','T','T_NEW');
PL/SQL procedure successfully completed.
SQL> select count(*) from t_new;
COUNT(*)
----------
13607
SQL> SELECT partitioned FROM user_tables WHERE table_name = 'T_NEW';
PAR
---
NO
SQL> SELECT partitioned FROM user_tables WHERE table_name = 'T';
PAR
---
YES
SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'T'
PARTITION_NAME
------------------------------
T_2013
T_2014
T_2015
SQL>
至此重定义完成，将T转变成了分区表。
在线重定义的一些约束：
1.需要有足够的空间来存放该表的2份数据。
2.不能变更主键的字段
3.表必须有主键
4.重定义只能在相同的schema中
5.在重定义完成之前增加新列不能指定NOT NULL
6.表不能包含LONG、BFILE、或用户定义类型的字段。
7.簇表不能重定义
8.SYS、SYSTEM的表不能重定义
9.表上有物化视图日志，或表上有定义物化视图，不能重定义
10.Horizontal sub setting of data cannot be performed during the redefinition
--=======================
和在线重定义有关的试图有下面2张：
select * from dba_redefinition_errors
select * from dba_redefinition_objects