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

来源：互联网发布：淘宝客服链接网址编辑：程序博客网时间：2024/05/17 21:48

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

一：源表和索引创建

源表创建

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;

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

阅读全文

0 0