ORACLE重定义已经分区的大表
Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。
本次分享背景：环境：ORACLE 11.2.0.4.0
对象：CON_CONTENT_HISTORY
该表行数：4.8亿，原来的分区列是CREATETM，按照时间按月进行范围分区；
需求说明详见下图
具体操作说明:
【注意事项】分区在线重定义必须源表上面有主键，如果没有主键则利用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、创建临时表，临时表跟源表结构一致，但分区列为我们所需的；用时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');
【开始操作】尽量选择业务不忙的时候，由于该表白天使用较为频繁，故是晚上进行操作
1、重定义表结构；由于源表和临时表结构相同，故直接如下写：
此过程比较消耗时间，会把中间表填满数据，所以此时要有足够的空间产生新中间表数据,做此操作之前查看空间是否够用
此处用时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获取创建索引的语句，然后创建到临时表上面
鲁豫并行创建索引用时为500秒；华东用时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、结束重定义
鲁豫用时36秒，华东用时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、自我验证
1）查看失效对象，并重新编译
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';
2）查看现在的这个表是不是分区表
select partitioned from user_tables where table_name = 'CON_CONTENT_HISTORY';
3)查看这个表的分区列是不是我们所需求的
select * fromuser_PART_KEY_COLUMNS where name=upper('con_content_history')
4）查看数据是否落在分区里面
select count(*) from con_content_historypartition(SYS_P6505)wherecontent_date >= TO_DATE('2016-01-18', 'YYYY-MM-DD')
4、待验证通过后,删掉临时表
BEGIN
truncate table cyt;--因为是大表，建议使用该方式
drop table cyt; --删除临时表的定义
END;
一般都是普通表进行分区重定义，很少遇到本身是分区表的在线重定义，先留着。 :lol膜拜一下 高人
页:
[1]