ORACLE普通表转换成分区表的操作——在线重定义表(DBMS_REDEFINITION)

1.操作用户需要有以下权限
GRANT EXECUTE ON DBMS_REDEFINITION TO HAGJ;
2.需要分区的表
select * from shiyu;

3.表结构

CREATE TABLE HAGJ.SHIYU
(
  LOGID         CHAR(16 BYTE),
  CREATETIME    CHAR(19 BYTE),
  LOGTYPE       VARCHAR2(10 BYTE),
  SAFETYLEVEL   INTEGER,
  TRANID        CHAR(36 BYTE),
  USERID        CHAR(11 BYTE),
  ENTID         CHAR(11 BYTE),
  ORGID         CHAR(11 BYTE),
  DEPTID        CHAR(11 BYTE),
  PROGRAMID     VARCHAR2(120 BYTE),
  LOGDESC       NVARCHAR2(256),
  OPRESULT      NVARCHAR2(256),
  COMPUTERNAME  VARCHAR2(64 BYTE),
  IP            VARCHAR2(40 BYTE),
  FORMID        CHAR(16 BYTE),
  MILLISECONDS  INTEGER,
  "QyUserId"    CHAR(11 BYTE),
  QYUSER        NVARCHAR2(20)
)

4.创建一个与shiyu 表字段相同，但是分区的表
CREATE TABLE shiyu_temp
(
  LOGID         CHAR(16 BYTE),
  CREATETIME    CHAR(19 BYTE),
  LOGTYPE       VARCHAR2(10 BYTE),
  SAFETYLEVEL   INTEGER,
  TRANID        CHAR(36 BYTE),
  USERID        CHAR(11 BYTE),
  ENTID         CHAR(11 BYTE),
  ORGID         CHAR(11 BYTE),
  DEPTID        CHAR(11 BYTE),
  PROGRAMID     VARCHAR2(120 BYTE),
  LOGDESC       NVARCHAR2(256),
  OPRESULT      NVARCHAR2(256),
  COMPUTERNAME  VARCHAR2(64 BYTE),
  IP            VARCHAR2(40 BYTE),
  FORMID        CHAR(16 BYTE),
  MILLISECONDS  INTEGER,
  "QyUserId"    CHAR(11 BYTE),
  QYUSER        NVARCHAR2(20)
)partition by range(CREATETIME)
(
    partition p1 values less than ('2017-06-01 00:00:00'),
    partition p2 values less than ('2017-09-01 00:00:00'),
    partition p3 values less than ('2017-12-01 00:00:00')
    
);


5.开始在线重定义
SQL> EXEC dbms_redefinition.start_redef_table('HAGJ', 'SHIYU','SHIYU_TEMP');
BEGIN dbms_redefinition.start_redef_table('HAGJ', 'SHIYU','SHIYU_TEMP'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "HAGJ"."SHIYU" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 1

6.原表需要有主键,这里先添加主键
SQL> alter table shiyu add constraint pk_shiyu primary key(logid);

7.开始执行在线重定义
SQL> EXEC dbms_redefinition.start_redef_table('HAGJ', 'SHIYU','SHIYU_TEMP');

PL/SQL procedure successfully completed.

8.如果表的数据很多，7步的时候可能会很长，这期间系统可能会继续对表SHIYU进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HAGJ', 'SHIYU','SHIYU_TEMP');
END;
/

SQL> DECLARE
  2  error_count pls_integer := 0;
  3  BEGIN
  4  dbms_redefinition.copy_table_dependents('HAGJ', 'SHIYU','SHIYU_TEMP',0, true, false, true, false,error_count);
  5  dbms_output.put_line('errors := ' || to_char(error_count));
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

9.结束在线重定义
SQL>    EXEC  dbms_redefinition.finish_redef_table('HAGJ', 'SHIYU','SHIYU_TEMP');

PL/SQL procedure successfully completed

10.查看是否转换成功
SQL> select count(*) from shiyu partition(P2);

  COUNT(*)
----------
   2248424

SQL> select count(*) from shiyu partition(p3);

  COUNT(*)
----------
      3081

SQL>

11.删除中间表

SQL> drop table shiyu_temp;

Table dropped.