Oracle中允许对表和分区表进行在线(联机)重定义,这给数据库结构的修改和定义带来了很大的方便。
创建一个分区表
验证表TAB_UNPART是否可以在线重定义,如果不可以会给出建议
cannot online redefine table “TEST”.”TAB_UNPART” with no primary key,原因是表TAB_UNPART没有主键。
再次验证
使用DBMS_REDEFINITION包将非分区表转为分区表
成功转换
如果执行在线重定义的过程中出错
可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行:DBMS_REDEFINITION.abort_redef_table(‘TEST’, ‘TAB_UNPART’, ‘TAB_PART’);以放弃执行在线重定义。。
如果出现以下错误,可以用删除物化视图日志或者物化视图来解决。
drop materialized view log on tablename;
或者
drop materialized view viewname;
下面的例子是将一个普通的表在线重定义为分区表。
创建一个非分区表,注意两个表的表结构和字段类型一致。
SQL> CREATE TABLE tab_unpart
2 (
3 col1 varchar2(30),
4 col2 DATE
5 );
TABLE created.
SQL> INSERT INTO tab_unpart(col1, col2) SELECT -1, sysdate-365 FROM dual;
1 ROW created.
SQL> INSERT INTO tab_unpart(col1, col2) SELECT 0, sysdate FROM dual;
1 ROW created.
SQL> INSERT INTO tab_unpart(col1, col2) SELECT +1, sysdate+360 FROM dual;
1 ROW created.
SQL> commit;
Commit complete.
SQL> SELECT col1,to_char(col2,'yyyy-mm-dd') FROM tab_unpart;
COL TO_CHAR(CO
--- ----------
-1 2011-01-05
0 2012-01-05
1 2012-12-30
创建一个分区表
SQL> CREATE TABLE tab_part
2 (
3 col1 varchar2(30),
4 col2 DATE
5 )
6 partition BY range(col2)
7 (
8 partition tab_part_2011 VALUES less than (to_date('2011-01-01','yyyy-mm-dd')),
9 partition tab_part_2012 VALUES less than (to_date('2012-01-01','yyyy-mm-dd')),
10 partition tab_part_2013 VALUES less than (to_date('2013-01-01','yyyy-mm-dd')),
11 );
TABLE created.
SQL> @partition_
NAME OBJECT COLUMN_NAME PARTITIONING_TYPE STATUS PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2013
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2011
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2012
验证表TAB_UNPART是否可以在线重定义,如果不可以会给出建议
SQL> EXEC dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1);
BEGIN dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine TABLE "TEST"."TAB_UNPART" 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
cannot online redefine table “TEST”.”TAB_UNPART” with no primary key,原因是表TAB_UNPART没有主键。
SQL> ALTER TABLE TAB_UNPART ADD CONSTRAINT pk_unpart_col1 PRIMARY KEY (COL1);
TABLE altered.
SQL> ALTER TABLE TAB_PART ADD CONSTRAINT pk_part_col1 PRIMARY KEY (COL1);
TABLE altered.
再次验证
SQL> EXEC dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1);
PL/SQL PROCEDURE successfully completed.
使用DBMS_REDEFINITION包将非分区表转为分区表
SQL> @partition_
NAME OBJECT COLUMN_NAME PARTITIONING_TYPE STATUS PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2013
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2011
TAB_PART TABLE COL2 RANGE VALID TAB_PART_2012
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TAB_UNPART',2);
3 DBMS_REDEFINITION.START_REDEF_TABLE('TEST','TAB_UNPART','TAB_PART',NULL,2);
4 DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TAB_UNPART','TAB_PART');
5 END;
6 /
PL/SQL PROCEDURE successfully completed.
SQL> @partition_
NAME OBJECT COLUMN_NAME PARTITIONING_TYPE STATUS PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_UNPART TABLE COL2 RANGE VALID TAB_PART_2013
TAB_UNPART TABLE COL2 RANGE VALID TAB_PART_2011
TAB_UNPART TABLE COL2 RANGE VALID TAB_PART_2012
成功转换
SQL> SELECT * FROM tab_unpart partition (TAB_PART_2012);
COL COL2
--- ------------
-1 05-JAN-11
SQL> SELECT * FROM tab_unpart partition (TAB_PART_2011);
no ROWS selected
SQL> SELECT * FROM tab_unpart partition (TAB_PART_2013);
COL COL2
--- ------------
0 05-JAN-12
1 30-DEC-12
如果执行在线重定义的过程中出错
可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行:DBMS_REDEFINITION.abort_redef_table(‘TEST’, ‘TAB_UNPART’, ‘TAB_PART’);以放弃执行在线重定义。。
如果出现以下错误,可以用删除物化视图日志或者物化视图来解决。
drop materialized view log on tablename;
或者
drop materialized view viewname;
SQL> EXEC dbms_redefinition.can_redef_table('TEST','TAB_UNPART');
BEGIN dbms_redefinition.can_redef_table('TEST','TAB_UNPART'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine TABLE "TEST"."TAB_UNPART" WITH
materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION",line 137
ORA-06512: at "SYS.DBMS_REDEFINITION",line 1478
ORA-06512: at line 1