忘记密码   免费注册 查看新帖 | 论坛精华区

ChinaUnix.net

  平台 论坛 博客 认证专区 大话IT 视频 徽章 文库 沙龙 自测 下载 频道自动化运维 虚拟化 储存备份 C/C++ PHP MySQL 嵌入式 Linux系统
最近访问板块 发新帖
查看: 6236 | 回复: 2

[分享] ORACLE重定义已经分区的大表 [复制链接]

论坛徽章:
0
发表于 2016-06-12 14:49 |显示全部楼层

     Oracle从9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的同时允许DML操作。
     本次分享背景:环境:ORACLE 11.2.0.4.0
                        对象:CON_CONTENT_HISTORY
                        该表行数:4.8亿,原来的分区列是CREATETM,按照时间按月进行范围分区;
                        需求说明详见下图
                        QQ图片20160612141319.png
     具体操作说明:
     【注意事项】分区在线重定义必须源表上面有主键,如果没有主键则利用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 * from  user_PART_KEY_COLUMNS where name=upper('con_content_history')

4)查看数据是否落在分区里面
select count(*) from con_content_history  partition(SYS_P6505)  where  content_date >= TO_DATE('2016-01-18', 'YYYY-MM-DD')


4、待验证通过后,删掉临时表
BEGIN
  truncate table cyt;--因为是大表,建议使用该方式
  drop table cyt; --删除临时表的定义
END;

论坛徽章:
0
发表于 2016-06-12 15:39 |显示全部楼层
一般都是普通表进行分区重定义,很少遇到本身是分区表的在线重定义,先留着。

论坛徽章:
12
数据库技术版块每日发帖之星
日期:2015-07-08 22:20:00IT运维版块每日发帖之星
日期:2016-05-01 06:20:00IT运维版块每周发帖之星
日期:2016-03-07 16:27:44IT运维版块每月发帖之星
日期:2016-03-07 16:25:25IT运维版块每日发帖之星
日期:2016-02-21 06:20:00IT运维版块每日发帖之星
日期:2016-02-20 06:20:00数据库技术版块每日发帖之星
日期:2016-02-06 06:20:00IT运维版块每日发帖之星
日期:2016-01-30 06:20:00IT运维版块每日发帖之星
日期:2016-01-03 06:20:00IT运维版块每日发帖之星
日期:2015-11-29 06:20:00IT运维版块每日发帖之星
日期:2015-08-10 06:20:00数据库技术版块每日发帖之星
日期:2016-06-16 06:20:00
发表于 2016-06-13 10:12 |显示全部楼层
膜拜一下 高人
您需要登录后才可以回帖 登录 | 注册

本版积分规则

  

北京皓辰网域网络信息技术有限公司. 版权所有 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员  联系我们:
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP