Oracle 在线重定义(将普通堆表转换成分区表)

1 创建测试表

用sys用户创建测试表

SQL> CREATE TABLE HR.ST (ID NUMBER, TIME DATE);

Table created.

SQL> INSERT INTO HR.ST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

50416 rows created.

SQL> commit;

Commit complete.

2 授权

SQL> grant execute on DBMS_REDEFINITION to hr;

Grant succeeded.

SQL> grant CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE to hr;

Grant succeeded.

3 新建中间表(分区的表结构)

SQL> create table mid_st(id number primary key, time date) partition by range(time)

  2  (

  3    partition p1 values less than(to_date('2004-7-1', 'yyyy-mm-dd')),

  4    partition p2 values less than(to_date('2005-1-1', 'yyyy-mm-dd')),

  5    partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),

  6    partition p4 values less than(maxvalue)

  7  );

Table created

4 验证

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed

5 在线重定义

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

6 同步数据(可选)

如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

7 结束重定义

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

8 查看分区结果

SQL> select a.table_name, a.partition_name from user_tab_partitions a;

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

ST                             P1

ST                             P2

ST                             P3

ST                             P4

9 放弃在线重定义

可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：

DBMS_REDEFINITION.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。

10 常见问题

10.1 无主键

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "HR"."ST" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478

ORA-06512: at line 1

 

出错了, 该表上缺少主键, 为该表建主键. 再执行验证.

SQL> alter table st add constraint pk_t primary key(id);

Table altered.

 

用这句删除materialized view 即可继续进行

drop materialized view log on <tablename>;

drop materialized view log on t; OR drop materialized t;

10.2 未授权

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

begin DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST'); end;

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_REDEFINITION", line 50

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343

ORA-06512: at line 2