ORACLE在线重定义--将普通表转化为分区表
10gR2
需要将一个普通表转为按月分区提高查询效率
测试如下：
一、建立测试表
SQL> CREATE TABLE T(ID NUMBER ,TIME DATE);
Table created.
SQL> DESC T;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TIME DATE
SQL> INSERT INTO T SELECT ROWNUM,CREATED FROM ALL_OBJECTS;
17979 rows created.
SQL> SET TIMING ON
SQL> 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 declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
出错原因是没有赋予‘DBMS_REDEFINITION' 的执行权限。解决的办法是：
dba用户授予权限
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 key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
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('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:01
SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;
TO_CHAR(MAX(TIME),'
-------------------
2013-05-07 21:40:35
SQL> 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 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-
