溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關數據庫中怎么將一個普通表轉換為分區表的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。
各位技術愛好者，看完本文后，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 將一個普通表轉換為分區表的常用方法（重點）
② 在線重定義的使用
③ ctas和insert的優化
④ DML語句如何開啟并行操作，如何查看DML是否開啟了并行
Tips：
① 若文章代碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：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皆可，您的批評指正是我寫作的最大動力。
參考文檔都是MOS上How to Partition a Non-partitioned / Regular / Normal Table (文檔 ID 1070693.6)，已上傳到云盤，大家可自行下載。
本文介紹了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
采用邏輯導出導入很簡單，首先在源庫建立分區表，然后將數據導出，然后導入到新建的分區表即可，
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
創建普通表并插入測試數據
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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>
這種方法的特點是:
優點：方法簡單易用，由于采用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成后數據已經在分布到各個分區中了。
不足：對于數據的一致性方面還需要額外的考慮。由于幾乎沒有辦法通過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句后對數據進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。
適用于修改不頻繁的表，在閑時進行操作，表的數據量不宜太大。
主要有2種方式，ctas和insert方式，下邊分別介紹：
利用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>
對于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
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 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) |
| 3 | LOAD AS SELECT | T_NEW | | | | | Q1,00 | PCWP | |
| 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也是并行的，這在源表的數據量非常大的情況下性能顯著。
這種方法就是先建立表結構然后使用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>
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
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | |
| 1 | LOAD AS SELECT | T_LHR_20160527 | | | | | | | |
| 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
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 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) |
| 3 | LOAD AS SELECT | T_LHR_20160527 | | | | | Q1,00 | PCWP | |
| 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>
這種方法的特點
優點：只是對數據字典中分區和表的定義進行了修改，沒有數據的修改或復制，效率最高。如果對數據在分區中的分布沒有進一步要求的話，實現比較簡單。在執行完RENAME操作后，可以檢查T_OLD中是否存在數據，如果存在的話，直接將這些數據插入到T中，可以保證對T插入的操作不會丟失。
不足：仍然存在一致性問題，交換分區之后RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到數據。如果要求數據分布到多個分區中，則需要進行分區的SPLIT操作，會增加操作的復雜度，效率也會降低。
適用于包含大數據量的表轉到分區表中的一個分區的操作。應盡量在閑時進行操作。
舉例來說明
創建普通表并插入測試數據
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
交換分區的操作步驟如下：
1. 創建分區表，假設有2個分區，P1，P2.
2. 創建表A存放P1規則的數據。
3. 創建表B 存放P2規則的數據。
4. 用表A 和P1 分區交換。 把表A的數據放到到P1分區
5. 用表B 和p2 分區交換。 把表B的數據存放到P2分區。
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.
這種分區的特點
優點：保證數據的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
不足：實現上比上面兩種略顯復雜。
適用于各種情況。
在線重定義的大致操作流程如下：
（1）創建基礎表A，如果存在，就不需要操作。
（2）創建臨時的分區表B結構。
（3）開始重定義，將基表A的數據導入臨時分區表B。
（4）結束重定義，完成后在DB的 Name Directory里，已經將2個表進行了交換。即此時基表A成了分區表，我們創建的臨時分區表B 成了普通表。 此時我們可以刪除我們創建的臨時表B。它已經是普通表。
MOS上的文檔：
這個功能只在9.2.0.4以后的版本才有，在線重定義表具有以下功能：
（1）修改表的存儲參數；
（2）將表轉移到其他表空間；
（3）增加并行查詢選項；
（4）增加或刪除分區；
（5）重建表以減少碎片；
（6）將堆表改為索引組織表或相反的操作；
（7）增加或刪除一個列。
在線重定義的原理：物化視圖
在線重定義表的步驟：
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;
使用在線重定義的一些限制條件：
（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
創建普通表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
--------------------------------------------------------------------------------------------------------------
感謝各位的閱讀！關于“數據庫中怎么將一個普通表轉換為分區表”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
