ORACLE重定義已經(jīng)分區(qū)的大表

caoyutingtjpu

     Oracle從9i版本開始提供了在線重定義表功能，通過調(diào)用DBMS_REDEFINITION包，可以在修改表結(jié)構(gòu)的同時(shí)允許DML操作。
     本次分享背景：環(huán)境：ORACLE 11.2.0.4.0
                        對(duì)象：CON_CONTENT_HISTORY
                        該表行數(shù)：4.8億，原來的分區(qū)列是CREATETM，按照時(shí)間按月進(jìn)行范圍分區(qū)；
                        需求說明詳見下圖
                       
     具體操作說明:
     【注意事項(xiàng)】分區(qū)在線重定義必須源表上面有主鍵，如果沒有主鍵則利用rowid；
     【思路】普通表在線重定義分區(qū)做法類似
     【準(zhǔn)備工作】
1、與業(yè)務(wù)人員溝通后，添加如下索引；耗時(shí)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、創(chuàng)建臨時(shí)表，臨時(shí)表跟源表結(jié)構(gòu)一致，但分區(qū)列為我們所需的；用時(shí)3s。（11g利用interval可以自動(dòng)創(chuàng)建分區(qū))；創(chuàng)建時(shí)表名盡量跟線上規(guī)則不一致，且表所在空間足夠容納大表。
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、檢查重定義的合理性；如果不能重定義，會(huì)顯示具體的原因。（第一個(gè)是用戶名，第二個(gè)是寫需要重定義的表名）
exec dbms_redefinition.can_redef_table('usr_wms_city', ' CON_CONTENT_HISTORY');

【開始操作】盡量選擇業(yè)務(wù)不忙的時(shí)候，由于該表白天使用較為頻繁，故是晚上進(jìn)行操作
1、重定義表結(jié)構(gòu)；由于源表和臨時(shí)表結(jié)構(gòu)相同，故直接如下寫：
          此過程比較消耗時(shí)間，會(huì)把中間表填滿數(shù)據(jù)，所以此時(shí)要有足夠的空間產(chǎn)生新中間表數(shù)據(jù),做此操作之前查看空間是否夠用
    此處用時(shí)2721.265秒

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'usr_wms_city',
orig_table => 'CON_CONTENT_HISTORY',
int_table => 'cyt');
END;

--如果結(jié)構(gòu)不同
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'usr_wms_city',
'CON_CONTENT_HISTORY',
'cyt',
'cell_id cell_id,.......', -- 在這里指定新的映射關(guān)系
DBMS_REDEFINITION.CONS_USE_PK);

如果中途出現(xiàn)意外execute dbms_redefinition.abort_redef_table('usr_wms_city','con_content_history','cyt');使用該語句回滾

2、同步臨時(shí)表，時(shí)間較快，只是同步從開始轉(zhuǎn)換到現(xiàn)在產(chǎn)生的新數(shù)據(jù)
    此處用時(shí)35.594秒
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'usr_wms_city',
orig_table => 'CON_CONTENT_HISTORY',
int_table => 'cyt');
END;

3、創(chuàng)建新表的索引，在線重定義只定義數(shù)據(jù)，不建立索引
用下面的SQL獲取創(chuàng)建索引的語句，然后創(chuàng)建到臨時(shí)表上面

魯豫并行創(chuàng)建索引用時(shí)為500秒；華東用時(shí)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、收集臨時(shí)表的統(tǒng)計(jì)信息
    此處用時(shí)1721.672秒
exec dbms_stats.gather_table_stats('usr_wms_city', 'cyt', cascade => true);

5、結(jié)束重定義
魯豫用時(shí)36秒，華東用時(shí)142.328秒
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => 'usr_wms_city',
                                       orig_table => 'con_content_history',
                                       int_table  => 'cyt'
                                       );
END;

【檢查】
1、查看現(xiàn)在的源表的索引，是否與之前一致


2、一定要注意取消索引并行度

select *  from dba_indexes where degree >1

alter index PK_CON_CONTENT_H_CELL_ID1 noparallel;

3、自我驗(yàn)證
1）查看失效對(duì)象，并重新編譯
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）查看現(xiàn)在的這個(gè)表是不是分區(qū)表
select partitioned from user_tables where table_name = 'CON_CONTENT_HISTORY';

3)查看這個(gè)表的分區(qū)列是不是我們所需求的
select * from  user_PART_KEY_COLUMNS where name=upper('con_content_history')

4）查看數(shù)據(jù)是否落在分區(qū)里面
select count(*) from con_content_history  partition(SYS_P6505)  where  content_date >= TO_DATE('2016-01-18', 'YYYY-MM-DD')


4、待驗(yàn)證通過后,刪掉臨時(shí)表
BEGIN
  truncate table cyt;--因?yàn)槭谴蟊�，建議使用該方式
  drop table cyt; --刪除臨時(shí)表的定義
END;

maohaiqing0304

一般都是普通表進(jìn)行分區(qū)重定義，很少遇到本身是分區(qū)表的在線重定義，先留著。

rtm009

膜拜一下 高人