【分割槽】如何將一個普通錶轉換為分割槽表

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 將一個普通錶轉換為分割槽表的常用方法（重點）

② 線上重定義的使用

③ ctas和insert的最佳化

④ DML語句如何開啟並行操作，如何檢視DML是否開啟了並行

Tips：

① 若文章程式碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：（提取碼：ed9b）

② 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- ------------------- ---------- ---------

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

1.2.2 相關參考文章連結

參考文件都是MOS上How to Partition a Non-partitioned / Regular / Normal Table (文件 ID 1070693.6)，已上傳到雲盤，大家可自行下載。

1.2.3 本文簡介

本文介紹了4種非分割槽錶轉換為分割槽表的幾種方法，參考文件來自於MOS。

將普通錶轉換成分割槽表有4種方法，這個在MOS文件上有說明（How to Partition a Non-partitioned / Regular / Normal Table (文件 ID 1070693.6)）：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

---------------------------------------------------------------------------------------------------------------------

第二章非分割槽錶轉換為分割槽表的4種方法

2.1 Export/import method

採用邏輯匯出匯入很簡單，首先在源庫建立分割槽表，然後將資料匯出，然後匯入到新建的分割槽表即可，

1) 匯出表：exp usr/pswd tables=numbers file=exp.dmp

2) 刪除表：drop table numbers;

3) 重建分割槽表的定義：

create table numbers (qty number(3), name varchar2(15))

partition by range (qty)

(partition p1 values less than (501),

partition p2 values less than (maxvalue));

4) 利用ignore=y來匯入分割槽表：imp usr/pswd file=exp.dmp ignore=y

2.1.1 示例

建立普通表並插入測試資料

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87069 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1107

採用expdp匯出表

[ZFXDESKDB2:oracle]:/tmp>expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:\"IN $\'T\'$\" SCHEMAS=LHR LOGFILE=expdp_T.log

Export: Release 11.2.0.4.0 - Production on Fri May 27 11:07:46 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:"IN ('T')" SCHEMAS=LHR LOGFILE=expdp_T.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2 MB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "LHR"."T" 1.406 MB 87091 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/oracle/app/oracle/admin/dlhr/dpdump/lhr_t.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 27 11:07:57 2016 elapsed 0 00:00:11

刪除原表，建立一個分割槽表結構：

LHR@dlhr> drop table t;

Table dropped.

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )

2 PARTITION BY RANGE (TIME)

3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

5 PARTITION T3 VALUES LESS THAN (MAXVALUE))

6 ;

Table created.

LHR@dlhr>

匯入到分割槽表

[ZFXDESKDB2:oracle]:/tmp>impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log

Import: Release 11.2.0.4.0 - Production on Fri May 27 11:12:40 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Table "LHR"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "LHR"."T" 1.406 MB 87091 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri May 27 11:12:46 2016 elapsed 0 00:00:05

[ZFXDESKDB2:oracle]:/tmp>

查詢匯入後的情況：

SYS@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1083

SYS@dlhr> SELECT D.TABLE_OWNER,D.TABLE_NAME,D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='T';

TABLE_OWNER TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------ ------------------------------

LHR T T1

LHR T T2

LHR T T3

SYS@dlhr>

2.2 利用原表重建分割槽表(插入)

這種方法的特點是:

優點：方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分佈到各個分割槽中了。

不足：對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法透過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。

適用於修改不頻繁的表，在閒時進行操作，表的資料量不宜太大。

主要有2種方式，ctas和insert方式，下邊分別介紹：

2.2.1 例一：CTAS+RENAME

利用CTAS語法在建立分割槽表的時候可以一起插入資料，也可以建立好表結構再insert 進去。 CTAS這種方法採用DDL語句，不產生UNDO，只產生少量REDO，建表完成後資料已經在分佈到各個分割槽中。

建立普通表並插入測試資料

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87069 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1085

建立一個分割槽表，注意這裡的分割槽表的列後邊沒有資料型別：

LHR@dlhr> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

3 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

4 PARTITION T3 VALUES LESS THAN (MAXVALUE))

5 AS SELECT ID, TIME FROM T;

Table created.

LHR@dlhr>

改變表名

Table renamed.

LHR@dlhr> rename t_new to t;

Table renamed.

驗證新表資料

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1085

LHR@dlhr>

2.2.1.1 ctas效能提升

對於CTAS建表語句效能的提升可以透過如下的方式，① 加nologging ② 並行DDL ③ 查詢並行，需要說明的是建表完成後根據需要將表修改為logging模式。

CREATE TABLE T_NEW (ID, TIME)

PARTITION BY RANGE (TIME)

(PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

PARTITION T3 VALUES LESS THAN (MAXVALUE))

nologging parallel 4

AS SELECT /*+PARALLEL*/ ID, TIME FROM T;

執行計劃：

SYS@dlhr> explain plan for CREATE TABLE T_NEW (ID, TIME)

2 PARTITION BY RANGE (TIME)

3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

5 PARTITION T3 VALUES LESS THAN (MAXVALUE))

6 nologging parallel 4

7 AS SELECT /*+PARALLEL*/ ID, TIME FROM T;

Explained.

SYS@dlhr> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4064487821

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

| 0 | CREATE TABLE STATEMENT | | 82787 | 1778K| 14 (0)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 4 | PX BLOCK ITERATOR | | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL | T | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWP | |

----------------------------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

- automatic DOP: skipped because of IO calibrate statistics are missing

17 rows selected.

SYS@dlhr>

可以看到對T表的查詢是並行的，create table也是並行的，這在源表的資料量非常大的情況下效能顯著。

2.2.2 例二: Insert with a subquery method

這種方法就是先建立表結構然後使用insert 來實現。

看示例：

建立普通表T_LHR_20160527

LHR@dlhr> CREATE TABLE T_LHR_20160527 (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T_LHR_20160527 SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87098 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from T_LHR_20160527 t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1114

建立一個分割槽表T_LHR_20160527_NEW：

LHR@dlhr> CREATE TABLE T_LHR_20160527_NEW (ID NUMBER, TIME DATE)

2 PARTITION BY RANGE (TIME)

3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

5 PARTITION T3 VALUES LESS THAN (MAXVALUE));

Table created.

從源表查詢插入到新表中：

LHR@dlhr> alter table T_LHR_20160527_NEW nologging;

Table altered.

LHR@dlhr> alter session enable parallel dml;

Session altered.

LHR@dlhr> insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select * from T_LHR_20160527;

87098 rows created.

LHR@dlhr> commit;

Commit complete.

刪除源表，重新命名新表

LHR@dlhr> drop table T_LHR_20160527;

Table dropped.

LHR@dlhr> rename T_LHR_20160527_NEW to T_LHR_20160527;

Table renamed.

驗證新表資料：

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from T_LHR_20160527 t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1114

LHR@dlhr>

2.2.2.1 insert效能提升

INSERT效能提升的方式，① 表修改為nologging ② 禁用表上的索引，可以將資料插入完成後再建索引 ③ 啟用並行DML alter session enable parallel dml; ④ 採用 append方式插入

commit;

alter session enable parallel dml;

alter table T_LHR_20160527_NEW nologging;

insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select /*+PARALLEL(t3,4)*/ * from T_LHR_20160527;

採用並行DML必須執行alter session enable parallel dml;才可以啟用並行DML,執行計劃:

LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3;

Explained.

LHR@dlhr> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 584641640

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | |

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM)| :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) |

| 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

- automatic DOP: skipped because of IO calibrate statistics are missing

17 rows selected.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> alter session enable parallel dml;

Session altered.

LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3;

Explained.

LHR@dlhr> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 576433284

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) |

| 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

- automatic DOP: skipped because of IO calibrate statistics are missing

17 rows selected.

LHR@dlhr>

2.3 使用交換分割槽的方法(Partition exchange method)

這種方法的特點

優點：只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分割槽中的分佈沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。

不足：仍然存在一致性問題，交換分割槽之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分佈到多個分割槽中，則需要進行分割槽的SPLIT操作，會增加操作的複雜度，效率也會降低。

適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。應儘量在閒時進行操作。

2.3.1 單個分割槽示例

舉例來說明

建立普通表並插入測試資料

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS where CREATED<=to_date('201311','YYYYMM');

85984 rows created.

LHR@dlhr> COMMIT;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

建立分割槽表

LHR@dlhr> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION T1 VALUES LESS THAN (TO_DATE('2013-11-1', 'YYYY-MM-DD')),

3 PARTITION T2 VALUES LESS THAN (MAXVALUE));

Table created.

交換資料

LHR@dlhr> ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;

Table altered.

改變表名

LHR@dlhr> rename t to t_old;

Table renamed.

LHR@dlhr> rename t_new to t;

Table renamed.

查詢資料

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

2.3.2 多個分割槽示例

交換分割槽的操作步驟如下：

1. 建立分割槽表，假設有2個分割槽，P1，P2.

2. 建立表A存放P1規則的資料。

3. 建立表B 存放P2規則的資料。

4. 用表A 和P1 分割槽交換。把表A的資料放到到P1分割槽

5. 用表B 和p2 分割槽交換。把表B的資料存放到P2分割槽。

2.3.2.1 MOS上的例子

This example creates the exchange table with the same structure as the partitions of the partitioned table p_emp.

SQL> CREATE TABLE p_emp
2 (sal NUMBER(7,2))
3 PARTITION BY RANGE(sal)
4 (partition emp_p1 VALUES LESS THAN (2000),
5 partition emp_p2 VALUES LESS THAN (4000));

Table created.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000;

Table created.

SQL> CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;

Table created.

SQL> alter table p_emp exchange partition emp_p1 with table exchtab1;

Table altered.

SQL> alter table p_emp exchange partition emp_p2 with table exchtab2;

Table altered.

2.4 利用線上重定義功能(DBMS_REDEFINITION)

這種分割槽的特點

優點：保證資料的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

不足：實現上比上面兩種略顯複雜。

適用於各種情況。

線上重定義的大致操作流程如下：

（1）建立基礎表A，如果存在，就不需要操作。

（2）建立臨時的分割槽表B結構。

（3）開始重定義，將基表A的資料匯入臨時分割槽表B。

（4）結束重定義，完成後在DB的 Name Directory裡，已經將2個表進行了交換。即此時基表A成了分割槽表，我們建立的臨時分割槽表B 成了普通表。此時我們可以刪除我們建立的臨時表B。它已經是普通表。

MOS上的文件：

2.4.1 線上重定義的相關知識

2.4.1.1 線上重定義功能

這個功能只在9.2.0.4以後的版本才有，線上重定義表具有以下功能：

（1）修改表的儲存引數；

（2）將錶轉移到其他表空間；

（3）增加並行查詢選項；

（4）增加或刪除分割槽；

（5）重建表以減少碎片；

（6）將堆表改為索引組織表或相反的操作；

（7）增加或刪除一個列。

2.4.1.2 線上重定義表的步驟

線上重定義的原理：物化檢視

線上重定義表的步驟：

1.選擇一種重定義方法：

存在兩種重定義方法，一種是基於主鍵、另一種是基於ROWID。ROWID的方式不能用於索引組織表，而且重定義後會存在隱藏列M_ROW$$。預設採用主鍵的方式。

2.呼叫DBMS_REDEFINITION.CAN_REDEF_TABLE()過程，如果表不滿足重定義的條件，將會報錯並給出原因。

3.在用一個方案中建立一個空的中間表，根據重定義後你期望得到的結構建立中間表。比如：採用分割槽表，增加了COLUMN等。

4.呼叫DBMS_REDEFINITION.START_REDEF_TABLE()過程，並提供下列引數：被重定義的表的名稱、中間表的名稱、列的對映規則、重定義方法。

如果對映方法沒有提供，則認為所有包括在中間表中的列用於表的重定義。如果給出了對映方法，則只考慮對映方法中給出的列。如果沒有給出重定義方法，則認為使用主鍵方式。

5.在中間表上建立觸發器、索引和約束，並進行相應的授權。任何包含中間表的完整性約束應將狀態置為disabled。

當重定義完成時，中間表上建立的觸發器、索引、約束和授權將替換重定義表上的觸發器、索引、約束和授權。中間表上disabled的約束將在重定義表上enable。

6.（可選）如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。

7.執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程完成表的重定義。這個過程中，原始表會被獨佔模式鎖定一小段時間，具體時間和表的資料量有關。

執行完FINISH_REDEF_TABLE()過程後，原始表重定義後具有了中間表的屬性、索引、約束、授權和觸發器。中間表上disabled的約束在原始表上處於enabled狀態。

8.（可選）可以重新命名索引、觸發器和約束。對於採用了ROWID方式重定義的表，包括了一個隱含列M_ROW$$。推薦使用下列語句經隱含列置為UNUSED狀態或刪除。

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

2.4.1.3 使用線上重定義的限制條件

使用線上重定義的一些限制條件：

（1） There must be enough space to hold two copies of the table.

（2） Primary key columns cannot be modified.

（3） Tables must have primary keys.

（4） Redefinition must be done within the same schema.

（5） New columns added cannot be made NOT NULL until after the redefinition operation.

（6） Tables cannot contain LONGs, BFILEs or User Defined Types.

（7） Clustered tables cannot be redefined.

（8） Tables in the SYS or SYSTEM schema cannot be redefined.

（9） Tables with materialized view logs or materialized views defined on them cannot be redefined.

（10） Horizontal sub setting of data cannot be performed during the redefinition.

在Oracle 10.2.0.4和11.1.0.7 版本下，線上重定義可能會遇到如下bug：

Bug 7007594 - ORA-600 [12261]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

? 如果使用基於主鍵的方式，則原表後重定義後的表必須有相同的主鍵

? 如果使用基於ROWID的方式，則不能是索引組織表

? 如果原表上有物化檢視或者物化檢視日誌，則不能線上重定義

? 物化檢視容器表或者高階佇列表不能線上重定義

? 索引組織表的溢位表不能線上重定義

? 擁有BFILE，LOGN列的表不能線上重定義

? Cluster中的表不能線上重定義

? sys和system下的表不能線上重定義

? 臨時表不能線上重定義

? 不支援水平資料子集

? 在列對映時只能使用有確定結果的表示式，如子查詢就不行

? 如果中間表有新增列，則不能有NOT NULL約束

? 原表和中間表之間不能有引用完整性

? 線上重定義無法採用nologging

2.4.2 我的示例

建立普通表T_LHR_20160527_UNPART及其索引：

LHR@dlhr> CREATE TABLE T_LHR_20160527_UNPART (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T_LHR_20160527_UNPART SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87112 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> CREATE INDEX create_date_indx ON T_LHR_20160527_UNPART(TIME);

Index created.

LHR@dlhr> exec dbms_stats.gather_table_stats(user, 'T_LHR_20160527_UNPART', cascade => true);

PL/SQL procedure successfully completed.

LHR@dlhr>

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from T_LHR_20160527_UNPART t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1128

建立臨時分割槽表T_LHR_20160527_PART,注意這裡的time列我換成了CREATED_DATE

LHR@dlhr> CREATE TABLE T_LHR_20160527_PART (ID NUMBER PRIMARY KEY, CREATED_DATE DATE)

2 PARTITION BY RANGE (created_date)

3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

5 PARTITION T3 VALUES LESS THAN (MAXVALUE));

Table created.

然後執行DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', DBMS_REDEFINITION.CONS_USE_PK);檢查是否可以執行線上重定義，若返回錯誤的話說明不能執行，LHR@dlhr> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "LHR"."T" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 143

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635

ORA-06512: at line 1

LHR@dlhr> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

沒有錯誤，說明我們需要轉換的表可以執行線上重定義，下邊開始執行線上重定義，這個過程可能要等一會，根據表的大小不同而不同：

LHR@dlhr> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART',DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART',DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-42016: shape of interim table does not match specified column mapping

ORA-06512: at "SYS.DBMS_REDEFINITION", line 56

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498

ORA-06512: at line 1

LHR@dlhr> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART', 'ID ID, TIME created_date ', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

LHR@dlhr>

LHR@dlhr> select count(1) from T_LHR_20160527_UNPART;

COUNT(1)

----------

87112

LHR@dlhr> select count(1) from T_LHR_20160527_PART;

COUNT(1)

----------

87112

LHR@dlhr> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');

這一步操作結束後，資料就已經同步到這個臨時的分割槽表裡來了。需要注意的是如果分割槽表和原表列名相同，則可以不用加列的轉換，如果不同的話需要加上轉換，即重新指定對映關係。另外EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');是同步新表作用是可選的。如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。

下邊我們在新表上建立索引，線上重定義只重定義資料，索引還需要單獨建立。

LHR@dlhr> CREATE INDEX create_date_indx2 ON T_LHR_20160527_PART(created_date);

Index created.

LHR@dlhr> exec dbms_stats.gather_table_stats(user, 'T_LHR_20160527_PART', cascade => true);

PL/SQL procedure successfully completed.

LHR@dlhr>

接下來就是結束重定義了：

LHR@dlhr> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');

PL/SQL procedure successfully completed.

LHR@dlhr>

LHR@dlhr> select D.TABLE_NAME, partitioned from user_tables D where table_name like '%T_LHR_20160527%' ;

TABLE_NAME PAR

------------------------------ ---

T_LHR_20160527_PART NO

T_LHR_20160527_UNPART YES

LHR@dlhr> SELECT D.TABLE_NAME, partition_name

2 FROM user_tab_partitions D

3 WHERE table_name = 'T_LHR_20160527_UNPART';

TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

T_LHR_20160527_UNPART T1

T_LHR_20160527_UNPART T2

T_LHR_20160527_UNPART T3

LHR@dlhr>

結束重定義DBMS_REDEFINITION.FINISH_REDEF_TABLE的意義：

基表 T_LHR_20160527_UNPART和臨時分割槽表T_LHR_20160527_PART進行了交換。此時臨時分割槽表T_LHR_20160527_PART成了普通表，我們的基表T_LHR_20160527_UNPART成了分割槽表。

我們在重定義的時候，基表T_LHR_20160527_UNPART是可以進行DML操作的。只有在2個表進行切換的時候會有短暫的鎖表。

線上重定義能保證資料的一致性，在大部分時間內，表都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

還有最後一個步驟，刪除臨時表並索引重新命名，驗證資料即可：

LHR@dlhr> drop table T_LHR_20160527_PART;

Table dropped.

LHR@dlhr> alter index create_date_indx2 rename to create_date_indx;

Index altered.

LHR@dlhr> select to_char(t.created_date, 'YYYYMM'), COUNT(1)

2 from T_LHR_20160527_UNPART t

3 group by to_char(t.created_date, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1128

--------------------------------------------------------------------------------------------------------------

2.5 注意

文章中用的相關MOS文件已經上傳到雲盤大家可自行下載。

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2109454/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2016-05-23 10:00~ 2016-05-27 19:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。