ORACLE普通錶轉換成分割槽表的操作

【前言】oracle官方建議當表的大小大於2GB的時候就使用分割槽表進行管理，分割槽表相對於小的表管理和效能上都有很大的優勢，本文件暫時不介紹具體的優勢，主要介紹幾種普通錶轉換成分割槽表的方法；

【方法概述】oracle官方給了以下四種操作的方法：

 A)  Export/import method（匯入匯出）

 B)  Insert with a subquery method（插入子查詢的方法）

 C)  Partition exchange method（交換分割槽法）

 D)  DBMS_REDEFINITION（線上重定義）

這些方法的思路都是建立一個新的分割槽表，然後把舊錶的資料轉移到新表上面，接著轉移相應的依賴關係，最後進行表的重新命名，把新表和舊錶rename。
其中A、B、C這三種方法都會影響到系統的正常使用，本文件不做詳細的介紹，本文件主要介紹D方法，這種方法是目前普遍在進行普通錶轉換成分割槽表的方法。

【線上重定義進行分割槽表的操作】整個操作的思路如下，以SCOTT下的EMP表為例
1. 先確認下表能不能進行分割槽
基於主鍵的確認

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('SOCTT','EMP',DBMS_REDEFINITION.CONS_USE_PK);

END;

/

PL/SQL procedure successfully completed.顯示的是沒有問題的

2.進行臨時表的建立，以DEPTNO作為分割槽的選項
CREATE TABLE SCOTT.EMP_1
(
  EMPNONUMBER(4),
  ENAMEVARCHAR2(10 BYTE),
  JOBVARCHAR2(9 BYTE),
  MGRNUMBER(4),
  HIREDATEDATE,
  SALNUMBER(7,2),
  COMMNUMBER(7,2),
  DEPTNONUMBER(2)
)
PARTITION BY RANGE (DEPTNO)
(
  PARTITION EMP_A1 VALUES LESS THAN (20),
  PARTITION EMP_A2 VALUES LESS THAN (30),
  PARTITION EMP_A3 VALUES LESS THAN (40),
  PARTITION EMP_A4 VALUES LESS THAN (50),
  PARTITION EMP_A5 VALUES LESS THAN (60)
      )


3.開始執行資料的遷移
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_1');

4.如果表的資料很多，3步的時候可能會很長，這期間系統可能會繼續對錶EMP進行寫入或者更新資料，那麼可以執行以下的語句，這樣在執行最後一步的時候可以避免長時間的鎖定(該過程可選可不選)
BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_1');
END;
/

5.進行許可權物件的遷移
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'EMP','EMP_1',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

6.查詢相關錯誤，在操作之前先檢查，查詢DBA_REDEFINITION_ERRORS試圖查詢錯誤：
select object_name, base_table_name, ddl_txt from   DBA_REDEFINITION_ERRORS;

7.結束整個重定義
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_1');
END;
/

【總結】做過一個大小2.3GB，總行數360萬行的表，整個過程大概花了56秒的時間，整個過程還是相當快的。建議具體的生產環境的執行需要經過嚴格測試後執行，測試的過程中大概就能知道整個過程的執行時間長度。

另如果再執行的過程中發生錯誤，可以透過以下語句結束整個過程：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'EMP',
int_table => 'EMP_1'
);
END;