oracle 線上重定義

一、前言
    現在的業務應用，要求停機時間也非常短，有的甚至對停機零容忍。在這種情況下，有些業務在設計初期考慮不周全，到了一定時候必須要進行調整，比如說普通表到分割槽表的轉換問題。
    對於上億條記錄的普通表，轉換成分割槽表，按傳統的方式，肯定會有較長的停機時間。從oracle9i開始，提供了一個線上重定義的方式，只需要非常短的停機時間。

二、我們就線上重定義進行說明的舉例。
1、線上重定義，會佔用兩倍的表空間，所以要事先準備好
2、線上重定義的包
DBMS_REDEFINITION
對於這個包，裡面有很多專案，如下：
DBMS_REDEFINITION.START_REDEF_TABLE  這裡面主要有三個引數傳入：使用者、原表、建的中間表
DBMS_REDEFINITION.START_REDEF_TABLE  這裡面涉及到表列的修改的，主要是列名方面的修改，不能修改data type
dbms_redefinition.sync_interim_table 資料同步，在同步的過程中，可能產生較多的新資料，需要同步一下
DBMS_REDEFINITION.FINISH_REDEF_TABLE 對錶進行最後的定義修改
3、線上重定義是通過物化檢視來做的，如果在過程中失敗了，要重新開始，需要進行清理

三、案例說明

1、統計該表資訊
--資料量

SQL> select count(*) from mugua.mytest;
 
  COUNT(*)
----------
  45220113
 
--表定義
CREATE TABLE "MUGUA"."MYTEST"
   ( "MTREPORTSEQ_NO" NUMBER NOT NULL ENABLE,
 "GATEWAY_ID" VARCHAR2(20),
 "GMSG_ID" VARCHAR2(32) NOT NULL ENABLE,
 "SEND_STAT" VARCHAR2(50) NOT NULL ENABLE,
 "RECV_MOBILE" VARCHAR2(30),
 "RECV_TIME" DATE DEFAULT SYSDATE,
 "ERROR_CODE" VARCHAR2(20),
  CONSTRAINT "PK_MYTEST" PRIMARY KEY ("MTREPORTSEQ_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "test_IDX"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "test_DATA"

可以看到主鍵列：PK_MYTEST，為普通表

--索引情況
SQL> SELECT index_name,table_name,column_name FROM dba_ind_columns where table_name = 'MYTEST';
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------------------------------
PK_MYTEST                      MYTEST                          MTREPORTSEQ_NO

索引所在的空間：test_indx

--計劃分割槽欄位：recv_time

--主外來鍵：
主外來鍵：
SQL> select a.owner 主鍵擁有者,a.table_name 主鍵表,b.column_name 主鍵列,C.OWNER 外來鍵擁有者,c.table_name 外來鍵表,d.column_name 外來鍵列
  2  from user_constraints  a
  3  left join user_cons_columns b on  a.constraint_name=b.constraint_name left join user_constraints C  ON C.R_CONSTRAINT_NAME=a.constraint_name
  4  left join user_cons_columns d on c.constraint_name=d.constraint_name
  5  where  a.constraint_type='P' and  a.table_name='MYTEST' --需要檢視主外來鍵關係的表
  6  order by a.table_name

這個主鍵不存在對外來鍵的約束
2、資料表線上重定義
2.1根據原表定義建立中間表，建立相應的分割槽表，以recv_time為分割槽欄位
SQL>CREATE TABLE "MUGUA"."MYTEST_TMP"
   ( "MTREPORTSEQ_NO" NUMBER NOT NULL ENABLE,
 "GATEWAY_ID" VARCHAR2(20),
 "GMSG_ID" VARCHAR2(32) NOT NULL ENABLE,
 "SEND_STAT" VARCHAR2(50) NOT NULL ENABLE,
 "RECV_MOBILE" VARCHAR2(30),
 "RECV_TIME" DATE DEFAULT SYSDATE,
 "ERROR_CODE" VARCHAR2(20))
 partition by range(recv_time)
(partition y2011_q4 values less than (to_date('2012-01-01','yyyy-mm-dd')),
 partition y2012_q1 values less than (to_date('2012-04-01','yyyy-mm-dd')),
 partition y2012_q2 values less than (to_date('2012-07-01','yyyy-mm-dd')),
 partition y2012_q3 values less than (to_date('2012-10-01','yyyy-mm-dd')),
 partition y2012_q4 values less than (to_date('2013-01-01','yyyy-mm-dd')),
 partition year_q values less than (maxvalue));

Table created.

2.2檢查是否可以是否滿足線上定義的條件

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('MUGUA','MYTEST',1);

PL/SQL procedure successfully completed.

2.3使用線上定義包開始轉換原表資料及拷貝原表索引、trigger等等，注意同義詞不能拷貝

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE (uname => 'MUGUA',orig_table => 'MYTEST',int_table => 'MYTEST_TMP');
 
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  error_count pls_integer := 0;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname =>'MUGUA',orig_table  => 'MYTEST',int_table => 'MYTEST_TMP',ignore_errors=> TRUE,num_errors 
=>error_count);
  5  DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
  6  END;
  7  /

PL/SQL procedure successfully completed.
2.4因為是線上做的，所以有必要同步一下資料
SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => 'MUGUA',orig_table  => 'MYTEST',int_table  => 'MYTEST_TMP');
PL/SQL procedure successfully completed.

2.5收集中間表統計資訊，結束線上重定義工作
SQL> EXEC DBMS_STATS.gather_table_stats('MUGUA', 'MYTEST_TMP', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname   => 'MUGUA',orig_table  => 'MYTEST',int_table  => 'MYTEST_TMP');

PL/SQL procedure successfully completed.
2.6刪除中間表
SQL> DROP TABLE mugua.mytest_TMP PURGE;

Table dropped.
3主鍵索引不變，其它索引可以考慮成分割槽索引
本表除主鍵外沒有其它索引
四、資料剝離(以MYTEST為例)
1確定要剝離的表和分割槽等
--找出需要剝離的表和分割槽，記錄下表名及分割槽名，例如：要剝離這個分割槽：y2011_q4
SQL> select * from dba_tab_partitions where table_name = 'MYTEST' and owner = 'MUGUA';
--找出該表上的索引
SQL> SELECT index_name,table_name,column_name FROM dba_ind_columns where table_name = 'MYTEST';
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------------------------------
PK_MYTEST                      MYTEST                         MTREPORTSEQ_NO2
2建立對應的接收表
SQL> create table mugua.mytest_BAK20111230 as select * from mugua.mytest where 0 = 1;
3驗證資料
select count(*) from mugua.mytest partition(y2011_q4);
4資料剝離，並核對資料
SQL>alter table mugua.mytest exchange partition y2011_q4 with table  MUGUA.MYTEST_BAK20111230 without validation update global indexes;

SQL> select count(*) from MUGUA.MYTEST_BAK20111230;
5刪除原分割槽
SQL> alter table mugua.mytest drop partition y2011_q4 ;

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29371470/viewspace-1062756/，如需轉載，請註明出處，否則將追究法律責任。