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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324175/viewspace-2120937/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324175/viewspace-2120937/