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

红太狼 2022-06-05 01:22 258阅读 0赞

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.