ORACLE在线重定义--将普通表转化为分区表(二)
作
SQL> exec dbms_redefinition.start_redef_table('TEST','T','T_NEW');
PL/SQL procedure successfully completed.
一些问题：
建立过程关于物化视图的问题
SQL> desc user_mviews;
Name Null Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
MVIEW_NAME NOT NULL VARCHAR2(30)
CONTAINER_NAME NOT NULL VARCHAR2(30)
....省略
SQL> select mview_name from user_mviews;
no rows selected
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('test','T','T_NEW');
PL/SQL procedure successfully completed.
SQL> select mview_name from user_mviews;
MVIEW_NAME
------------------------------
T_NEW
SQL>
#start后会产生一个物化视图，如果中途失败不做abort会导致物化视图一次存在，下一次操作时会报：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('test', 'T', 'T_NEW');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('test', 'T', 'T_NEW'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "test"."T" with
materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
SQL> drop materialized view log on T;
ORA-14400: inserted partition key does not map to any partition
这个报错的意思是分区表建立时有值在分区表之外，未被包含，可以查询下数据把缺失的分区表建立起来
参考：
SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MAX(TIME),'
-------------------
2014-05-01 21:40:35
SQL> select partition_name from user_tab_partitions where table_name='T_NEW';
PARTITION_NAME
------------------------------
T_2003
T_2004
T_2005
T_2006
T_2007
T_2008
T_2009
T_2010
T_2011
T_2012
T_2013
11 rows selected.
SQL> ALTER TABLE T_NEW ADD PARTITION T_2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD'));
Table altered.
SQL> select partition_name from user_tab_partitions where table_name='T_NEW';
PARTITION_NAME
------------------------------
T_2003
T_2004
T_2005
T_2006
T_2007
T_2008
T_2009
T_2010
T_2011
T_2012
T_2013
T_2014
12 rows selected.
五、执行重定义后的分区数据同步
SQL> exec dbms_redefinition.sync_interim_table('TEST','T','T_NEW');
PL/SQL procedure successfully completed.
六、完成在线重定义操作
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','T','T_NEW');
PL/SQL procedure successfully completed.
如果执行在线重定义的过程中出错
可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里
执行：DBMS_REDEFINITION.abort_redef_table('test', 't', 't_new')以放弃执行在线重定义。