需要将一个普通表转为按月分区提高查询效率
测试如下：一、建立测试表SQL> CREATE TABLE T(ID NUMBER ,TIME DATE);Table created.SQL> DESC T; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER TIME DATESQL> INSERT INTO T SELECT ROWNUM,CREATED FROM ALL_OBJECTS;17979 rows created.SQL> SET TIMING ONSQL> select count(*) from t; COUNT(*)---------- 17979
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test','T', DBMS_REDEFINITION.CONS_USE_PK);BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('test','T', DBMS_REDEFINITION.CONS_USE_PK); END; *ERROR at line 1:ORA-06550: line 1, column 53:PLS-00201: identifier 'DBMS_REDEFINITION' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignored SQL> GRANT ALL ON SYS.DBMS_REDEFINITION TO TEST;Grant succeeded.SQL> GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE TO TEST;Grant succeeded.
回到test用户继续验证
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test', 'T', DBMS_REDEFINITION.CONS_USE_PK);BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('test', 'T', DBMS_REDEFINITION.CONS_USE_PK); END;*ERROR at line 1:ORA-12089: cannot online redefine table "test"."T" with no primary keyORA-06512: at "SYS.DBMS_REDEFINITION", line 137ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479ORA-06512: at line 1 如果没有定义主键会提示以上错误信息
建立主键：SQL> alter table t add constraint pk_t primary key(id);Table altered.
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test','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),'-------------------2003-06-13 21:11:01SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;TO_CHAR(MAX(TIME),'-------------------2013-05-07 21:40:35SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION T_2003 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD')), 3 PARTITION T_2004 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION T_2005 VALUES LESS THAN (TO_DATE('2006-1-1', 'YYYY-MM-DD')), 5 PARTITION T_2006 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')), 6 PARTITION T_2007 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')), 7 PARTITION T_2008 VALUES LESS THAN (TO_DATE('2009-1-1', 'YYYY-MM-DD')), 8 PARTITION T_2009 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD')), 9 PARTITION T_2010 VALUES LESS THAN (TO_DATE('2011-1-1', 'YYYY-MM-DD')), 10 PARTITION T_2011 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')), 11 PARTITION T_2012 VALUES LESS THAN (TO_DATE('2013-1-1', 'YYYY-MM-DD')), 12 PARTITION T_2013 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))); 四、在线重新定义操作
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 selectedSQL> 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_NEWSQL> #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" withmaterialized viewsORA-06512: at "SYS.DBMS_REDEFINITION", line 50ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343ORA-06512: at line 1SQL> 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_2003T_2004T_2005T_2006T_2007T_2008T_2009T_2010T_2011T_2012T_201311 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_2003T_2004T_2005T_2006T_2007T_2008T_2009T_2010T_2011T_2012T_2013T_201412 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')以放弃执行在线重定义。