oracle更改分区表结构,Oracle分区表在线重定义--更改分区列

Oracle分区表在线重定义--更改分区列

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

【背景说明】

Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。

本次分享背景：环境：ORACLE 11.2.0.4.0

对象：CON_CONTENT_HISTORY

该表行数：4.8亿，原来的分区列是CREATETM，按照时间按月进行范围分区；新的分区列是CONTENT_DATE

需求说明详见下图

【思路】

虽然源表已是分区表，但实际操作类似于普通表的在线重定义分区

【注意事项】分区在线重定义必须源表上面有主键，如果没有主键则利用rowid；

【准备工作】

1、与业务人员沟通后，添加如下索引；耗时2000.531s

ALTER TABLE CON_CONTENT_HISTORY ADD CONSTRAINTS PK_CON_CONTENT_H_HISTORY PRIMARY KEY (LOCNO, CELL_NO, CELL_ID, CONTENT_DATE) online parallel 8 ;

2、创建临时表，临时表跟源表结构一致，但分区列改为：CONTENT_DATE；用时3s。(11g利用interval可以自动创建分区)。

CREATE TABLE "CYT" --创建时表名尽量跟线上规则不一致，且表所在空间足够容纳大表

( "CELL_ID" NUMBER(15,0) NOT NULL ENABLE,

"LOCNO" VARCHAR2(10 CHAR) NOT NULL ENABLE,

"CELL_NO" VARCHAR2(24 CHAR) NOT NULL ENABLE,

"ITEM_NO" VARCHAR2(30 CHAR) NOT NULL ENABLE,

"BARCODE" VARCHAR2(32 CHAR) NOT NULL ENABLE,

"ITEM_TYPE" VARCHAR2(20 CHAR) DEFAULT '0' NOT NULL ENABLE,

"QUALITY" VARCHAR2(2 CHAR) DEFAULT '0' NOT NULL ENABLE,

"OWNER_NO" VARCHAR2(3 CHAR),

"SUPPLIER_NO" VARCHAR2(10 CHAR) DEFAULT 'N',

"QTY" NUMBER(18,5) DEFAULT 0 NOT NULL ENABLE,

"OUTSTOCK_QTY" NUMBER(18,5) DEFAULT 0 NOT NULL ENABLE,

"INSTOCK_QTY" NUMBER(18,5) DEFAULT 0 NOT NULL ENABLE,

"PACK_QTY" NUMBER(18,5) DEFAULT 1,

"UNUSUAL_QTY" NUMBER(18,5) DEFAULT 0,

"STATUS" VARCHAR2(1 CHAR) DEFAULT '0' NOT NULL ENABLE,

"HM_MANUAL_FLAG" VARCHAR2(1 CHAR) DEFAULT '1' NOT NULL ENABLE,

"CREATOR" VARCHAR2(20 CHAR) DEFAULT 'N',

"CREATETM" DATE,

"EDITOR" VARCHAR2(20 CHAR),

"EDITTM" DATE,

"CONTENT_DATE" DATE,

"SIZE_NO" VARCHAR2(10 CHAR)

)

PARTITION BY RANGE ("CONTENT_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))

3、检查重定义的合理性；如果不能重定义，会显示具体的原因。(第一参数是用户名，第二个参数是写需要重定义的表名)

exec dbms_redefinition.can_redef_table('usr_wms_city', ' CON_CONTENT_HISTORY');

【开始操作】

【注意】

虽然使用在线重定义对线上影响较小，但由于该表白天使用较为频繁，怕影响较大，故操作是晚上10点以后开始进行的

检查临时表cyt所在的空间是否够容纳大量数据

1、重定义表结构

此过程比较消耗时间，会把临时表cyt表填满数据，所以此时要有足够的空间。(做此操作之前一定要查看空间是否够用)

由于本环境源表和临时表结构相同，故按照下面第一个脚本执行，用时2721.265秒

【两表结构相同的脚本：】

BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'usr_wms_city',

orig_table => 'CON_CONTENT_HISTORY',

int_table => 'cyt');

END;

【两表结构不同的脚本：】

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

'usr_wms_city',

'CON_CONTENT_HISTORY',

'cyt',

'cell_id cell_id,.......', -- 在这里指定新的映射关系

DBMS_REDEFINITION.CONS_USE_PK);

【注意】

如果中途出现意外，使用如下语句回滚：

execute dbms_redefinition.abort_redef_table('usr_wms_city','con_content_history','cyt');

2、同步临时表

同步从开始转换到现在产生的新的数据，此处用时35.594秒

BEGIN

dbms_redefinition.sync_interim_table(

uname => 'usr_wms_city',

orig_table => 'CON_CONTENT_HISTORY',

int_table => 'cyt');

END;

3、创建新表的索引

在线重定义只定义数据，不建立索引

用下面的SQL获取创建索引的语句，然后创建到临时表上面

并行创建索引用时1059.468秒

select dbms_metadata.get_ddl('INDEX','IDX_CON_CONTENT_H1') from dual;

select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_CELL_ID1') from dual;

select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_HISTORY') from dual;

4、收集临时表的统计信息

此处用时1721.672秒

exec dbms_stats.gather_table_stats('usr_wms_city', 'cyt', cascade => true);

5、结束重定义

用时142.328秒

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'usr_wms_city',

orig_table => 'con_content_history',

int_table => 'cyt'

);

END;

至此，分区重定义结束

【检查】

1、查看表的索引和约束，是否与之前一致

略

2、取消索引的并行度

select * from dba_indexes where degree >1 --查看有没有并行创建的索引

alter index PK_CON_CONTENT_H_CELL_ID1 noparallel;--取消并行

3、编译失效的对象

下面脚本是检查是否有失效对象，并生成需要重新编译对象的SQL脚本

SELECT 'ALTER ' || (CASE

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'PACKAGE'

ELSE

OBJECT_TYPE

END) || ' ' || owner || '.' || OBJECT_NAME || ' COMPILE ' || (CASE

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'BODY;'

ELSE

';'

END),

owner,

OBJECT_NAME,

OBJECT_TYPE,

STATUS,

O.CREATED,

LAST_DDL_TIME

FROM dba_OBJECTS O

WHERE STATUS = 'INVALID';

4、验证分区

查看表是否为分区表

select partitioned from user_tables where table_name = 'CON_CONTENT_HISTORY';

查看表的分区列是不是业务所需求的分区列

select * from user_PART_KEY_COLUMNS where name=upper('con_content_history')

查看数据是否落在分区里面

select count(*) from con_content_history partition(SYS_P6505) where content_date >= TO_DATE('2016-01-18', 'YYYY-MM-DD')

5、删除临时表

待验证通过后,删掉临时表

BEGIN

truncate table cyt;--因为是大表，建议使用该方式

drop table cyt; --删除临时表的定义

END;