oracle在线将普通表转换成分区表

时间:2021-07-25 11:09:35

背景介绍:有时候随着业务的发展,需要将普通的表转换成分区表。这里主要介绍在线自动转换的方式。


一:源表和索引创建

源表创建

CREATE TABLE  EDC_SEPERATOR

(

  SEPERATOR_ID   NUMBER(15)                     NOT NULL,

  EQUIPMENTINFO  NVARCHAR2(20),

  RECORD         NVARCHAR2(50),

  TITLE          NVARCHAR2(50),

  ID             NVARCHAR2(50),

  TESTDATE       NVARCHAR2(50),

  TESTTIME       VARCHAR2(50 BYTE),

  COMT           NVARCHAR2(50),

  OPERATOR       NVARCHAR2(50),

  CLASSIFIC      NVARCHAR2(50),

  BIN            NVARCHAR2(50),

  UOC            NVARCHAR2(50),

  ISC            NVARCHAR2(50),

  RSERIEC        NVARCHAR2(50),

  RSH            NVARCHAR2(50),

  FF             NVARCHAR2(50),

  ETA            NVARCHAR2(50),

  IREV2          NVARCHAR2(50),

  TCELL          NVARCHAR2(50),

  TMONICELL      NVARCHAR2(50),

  INSOL          NVARCHAR2(50),

  UMPP           NVARCHAR2(50),

  IMPP           NVARCHAR2(50),

  PMPP           NVARCHAR2(50),

  JSC            NVARCHAR2(50),

  ENTITY_RRN     NUMBER(15),

  SORTER_SEQ     NUMBER(15),

  IMPORTTIME     DATE,

  DBIMPORTTIME   DATE

)

 

Table created.

源表建立索引


SQL> CREATE INDEX  INDX_EDC_SEPERATOR ON  EDC_SEPERATOR(IMPORTTIME);

 

Index created.

 

SQL> CREATE INDEX  INDX3_EDC_SEPERATOR ON  EDC_SEPERATOR(COMT);

 

Index created.


 二:开始在线重定义源表

如果有主键,基于主键重定义

 begin

 dbms_redefinition.can_redef_table('TEST','EDC_SEPERATOR');

 end;

没有主键,基于row_id重定义(两种写法)

1

begin

 dbms_redefinition.can_redef_table('TEST','EDC_SEPERATOR',2);

 end;

2:

begin

dbms_redefinition.can_redef_table('TEST','EDC_SEPERATOR',dbms_redefinition.cons_use_rowid);

end;

 

创建中间表

CREATE TABLE  TMP_P

(

  SEPERATOR_ID   NUMBER(15)                     NOT NULL,

  EQUIPMENTINFO  NVARCHAR2(20),

  RECORD         NVARCHAR2(50),

  TITLE          NVARCHAR2(50),

  ID             NVARCHAR2(50),

  TESTDATE       NVARCHAR2(50),

  TESTTIME       VARCHAR2(50 BYTE),

  COMT           NVARCHAR2(50),

  OPERATOR       NVARCHAR2(50),

  CLASSIFIC      NVARCHAR2(50),

  BIN            NVARCHAR2(50),

  UOC            NVARCHAR2(50),

  ISC            NVARCHAR2(50),

  RSERIEC        NVARCHAR2(50),

  RSH            NVARCHAR2(50),

  FF             NVARCHAR2(50),

  ETA            NVARCHAR2(50),

  IREV2          NVARCHAR2(50),

  TCELL          NVARCHAR2(50),

  TMONICELL      NVARCHAR2(50),

  INSOL          NVARCHAR2(50),

  UMPP           NVARCHAR2(50),

  IMPP           NVARCHAR2(50),

  PMPP           NVARCHAR2(50),

  JSC            NVARCHAR2(50),

  ENTITY_RRN     NUMBER(15),

  SORTER_SEQ     NUMBER(15),

  IMPORTTIME     DATE,

  DBIMPORTTIME   DATE

)

PARTITION BY RANGE (IMPORTTIME)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION P1 VALUES LESS THAN (to_date('20160101','YYYYMMDD'))

) TABLESPACE TEST

 

在线重定义

SQL> execute dbms_redefinition.start_redef_table('TEST','EDC_SEPERATOR','TMP_P',null,2);

 

PL/SQL procedure successfully completed.

 

在线同步

execute dbms_redefinition.sync_interim_table('TEST','EDC_SEPERATOR','TMP_P');

 

中间表创建索引

SQL> CREATE INDEX  INDX_P_SEPERATOR ON  TMP_P(IMPORTTIME);

 

Index created.

 

SQL> CREATE INDEX  INDX3_P_SEPERATOR ON  TMP_P (COMT);

Index created.

 

 

完成重定义

 

SQL> execute dbms_redefinition.finish_redef_table('TEST','EDC_SEPERATOR','TMP_P');

 

PL/SQL procedure successfully completed.

 

删除中间表

drop table tmp_p

 

检查是否已经分区

SQL>select TABLE_NAME,PARTITIONED from DBA_tables WHERE TABLE_NAME='EDC_SEPERATOR';

 

TABLE_NAME                     PAR

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

EDC_SEPERATOR                  YES