oracle 11g線上重構表

在Oracle9i出現之前，你只能透過MOVE或匯出和匯入的方式來進行表的重定義，因此表重定義的過程可能相當漫長或者說是一個離線過程，
在此期間應用程式對該表的操作將失敗。除了這個，如果用exp，我們也不能保證exp的時候該表的資料沒有改變（除非單使用者），
而imp更是一個漫長的過程。 為了解決這個問題，Oracle9i在其DBMS_REDEFINITION軟體包中引入了線上重定義功能。
這個特性對24*7的資料庫系統來說非常重要，使用這個技術DBA可以在保持表允許DML語句的情況下修改結構，比如新增列、移動表到其他表空間、
處理表的碎片等，當然了對於表的碎片處理，在10g以後，可以考慮使用shrink操作來實現，關於shrink在這裡不做討論。

線上重定義具有以下功能：

(1)修改表的儲存引數；
(2)可以將錶轉移到其他表空間；
(3)在表上增加、修改或刪除一列或是多列；
(4)增加並行查詢選項；
(5)增加分割槽支援；
(6)修改分割槽結構；
(7)重建表以減少碎片；
(8)將堆表改為索引組織表或相反的操作；

線上重定義的方法

1.基於主鍵
2.基於ROWID。ROWID的方式不能用於索引組織表，而且重定義後會存在隱藏列M_ROW$$。
預設採用主鍵的方式。

線上重定義的一些限制

1.要求原始表和中間表在同一個方案下；
2.要求有2倍甚至是多於2倍的表空間空間；
3.如果使用主鍵重定義的方式，原始表上要有主鍵；

線上定義出現異常執行下面停止定義
SQL>  execute dbms_redefinition.abort_redef_table('smsdb',' m_x_1','mp');
SQL> PL/SQL procedure successfully

模擬普通表重構成分割槽表，新增欄位操作。


--刪除之前測試資料表
--在DB使用者執行
SQL> DROP TABLE MXQ03;
 
Table dropped
SQL>  DROP TABLE MXQ04;
 
DROP TABLE MXQ04
 
Table dropped

--建立模擬資料
--建立普通表
SQL>  CREATE TABLE "DB"."MXQ03"
  2     (    "ID" NUMBER(10,0),
  3      "NAME" VARCHAR2(20),
  4      "NEW_DATE" DATE
  5     ) TABLESPACE "SMSDB_DATA";
 
Table created
SQL>    create index name_dex on MXQ03(name);
 
Index created
SQL>    alter table mxq03 add constraint pk_id_03 primary key(id);
 
Table altered
SQL>  INSERT INTO MXQ03 VALUES(1,'A',SYSDATE);
 
1 row inserted
SQL>  INSERT INTO MXQ03 VALUES(2,'B',SYSDATE);
 
1 row inserted
SQL>  INSERT INTO MXQ03 VALUES(3,'C',SYSDATE);
 
1 row inserted
SQL>  INSERT INTO MXQ03 VALUES(4,'D',SYSDATE);
 
1 row inserted
SQL>  INSERT INTO MXQ03 VALUES(5,'E',SYSDATE);
 
1 row inserted
SQL>  INSERT INTO MXQ03 VALUES(6,'F',SYSDATE);
 
1 row inserted
SQL> COMMIT;
 
Commit complete

建立分割槽臨時表
SQL>     CREATE TABLE "DB"."MXQ04"
  2     (    "ID" NUMBER(10,0),
  3      "NAME" VARCHAR2(20),
  4           "old" varchar2(20),
  5      "NEW_DATE" DATE)
  6     partition by range(ID)
  7     (partition mxq_3 values less than (3),
  8     partition mxq_6 values less thAn (10)) TABLESPACE "SMSDB_DATA";
 
Table created

--在SYS使用者執行
--判斷表是否支援重構
SQL> exec dbms_redefinition.can_redef_table('DB','MXQ03');
 
PL/SQL procedure successfully completed

--啟動重構表
SQL> exec dbms_redefinition.start_redef_table('DB','MXQ03','MXQ04','ID ID,NAME NAME,new_date new_date');
 
PL/SQL procedure successfully completed
 
--複製索引、主鍵、觸發器。。。。。
SQL> var v_log number;
SQL> exec dbms_redefinition.copy_table_dependents('sdb','mxq03','mxq04',NUM_ERRORS => :V_log);
 
PL/SQL procedure successfully completed
v_log
---------
0

--開始同步
SQL> exec dbms_redefinition.sync_interim_table('db','mxq03','mxq04');
 
PL/SQL procedure successfully completed
 
--完成同步
SQL> exec dbms_redefinition.finish_redef_table('db','mxq03','mxq04');
 
PL/SQL procedure successfully completed

--源表結構已經加上了old欄位
SQL> desc db.mxq03;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
ID       NUMBER(10)   Y                         
NAME     VARCHAR2(20) Y                         
old      VARCHAR2(20) Y                         
NEW_DATE DATE         Y                         
 
 
--查詢資料都已經同步
SQL> select * from db.mxq03;
 
         ID NAME                 old                  NEW_DATE
----------- -------------------- -------------------- -----------
          1 A                                         2015/5/28 1
          2 B                                         2015/5/28 1
          3 C                                         2015/5/28 1
          4 D                                         2015/5/28 1
          5 E                                         2015/5/28 1
          6 F                                         2015/5/28 1
 
6 rows selected
 
SQL> select * from db.mxq03 partition(mxq_3);
 
         ID NAME                 old                  NEW_DATE
----------- -------------------- -------------------- -----------
          1 A                                         2015/5/28 1
          2 B                                         2015/5/28 1
 
SQL> select * from db.mxq03 partition(mxq_6);
 
         ID NAME                 old                  NEW_DATE
----------- -------------------- -------------------- -----------
          3 C                                         2015/5/28 1
          4 D                                         2015/5/28 1
          5 E                                         2015/5/28 1
          6 F                                         2015/5/28 1