exchange partition的一些測試
線上重定義表可以將一個普通錶轉換成一個分割槽表。
exchange partition可以將一個分割槽表的一個分割槽和另一張表的資料互換,
也可以從普通表表遷移至分割槽表.
它透過更新資料字典來實現分割槽表與普通表的轉換，所以速度非常快
SQL> create table LGX_PARTAB(
2 A INT,
3 B DATE)
4 PARTITION BY RANGE(A)(
5 PARTITION PART01 VALUES LESS THAN(10),
6 PARTITION PART02 VALUES LESS THAN(20),
7 PARTITION PART03 VALUES LESS THAN(MAXVALUE));
Table created.
SQL> INSERT INTO LGX_PARTAB VALUES(1,SYSDATE);
1 row created.
SQL> INSERT INTO LGX_PARTAB VALUES(11,SYSDATE);
1 row created.
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART01);
A B
---------- -------------------
1 2015/09/28 09:35:32
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A B
---------- -------------------
11 2015/09/28 09:35:39
SQL> CREATE TABLE LGX_TAB01(
2 A INT,
3 B DATE);
Table created.
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
Table altered.
SQL> select * from lgx_tab01;
A B
---------- -------------------
11 2015/09/28 09:35:39
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
no rows selected
SQL> INSERT INTO LGX_TAB01 VALUES(12,SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM LGX_TAB01;
A B
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
Table altered.
SQL> SELECT * FROM LGX_TAB01;
no rows selected
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A B
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> INSERT INTO LGX_TAB01 VALUES(30,SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
WITH TABLE LGX_TAB01
*
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01 WITHOUT VALIDATION;
Table altered.
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A B
---------- -------------------
30 2015/09/28 09:39:46
SQL> SELECT * FROM LGX_TAB01;
A B
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01 WITH VALIDATION;
Table altered.
SQL> SELECT * FROM LGX_TAB01;
A B
---------- -------------------
30 2015/09/28 09:39:46
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A B
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> DESC LGX_TAB01;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B DATE
SQL> DESC LGX_PARTAB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B DATE
SQL> alter table lgx_partab rename column b to c;
Table altered.
SQL> DESC LGX_PARTAB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
C DATE
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
WITH TABLE LGX_TAB01
*
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition
SQL> delete from lgx_tab01;
1 row deleted.
SQL> commit;
Commit complete.
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
Table altered.
SQL> SELECT * FROM LGX_TAB01;
A B
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
no rows selected
SQL> DESC LGX_TAB01;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B DATE
SQL> DESC LGX_PARTAB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
C DATE
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
Table altered.
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A C
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> SELECT * FROM LGX_TAB01;
no rows selected
SQL> ALTER TABLE LGX_TAB01 MODIFY (B VARCHAR2(30));
Table altered.
SQL> desc lgx_tab01;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B VARCHAR2(30)
SQL> select * from lgx_tab01;
no rows selected
SQL> desc lgx_partab;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
C DATE
SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);
A C
---------- -------------------
11 2015/09/28 09:35:39
12 2015/09/28 09:37:50
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01 WITH VALIDATION;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
2 WITH TABLE LGX_TAB01 WITHOUT VALIDATION;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL>
注意：
$涉及交換的兩表之間表結構必須一致，這裡的結構一致主要是指欄位數目必須相同而且資料型別必須相同，欄位的名字可以不同，如果
資料型別不同，即使加上without validation也沒有用，without validation的功能主要是用來驗證資料的。
$如果是從非分割槽表向分割槽表做交換，非分割槽表中的資料必須符合分割槽表中指定分割槽的規則，除非附加without validation子句;
$如果從分割槽表向分割槽表做交換，被交換的分割槽的資料必須符合分割槽規則，除非附加without validation子句;
$Global索引或涉及到資料改動了的global索引分割槽會被置為unusable，除非附加update indexes子句。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1810599/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- split partition的一些測試
- exchange partition
- exchange partition 的用法
- exchange partition(轉）
- Pruning、Reference Partition、Exchange Partition
- exchange partition原理探究
- exchange partition 實驗
- Exchange Partition的實驗例子
- [Oracle] Partition table exchange Heap tableOracle
- 巧妙使用exchange partition的一個案例
- Exchange partition分割槽結構的“乾坤挪移”
- 分割槽表UNUSED列後的EXCHANGE PARTITION操作
- 轉摘：EXCHANGE PARTITION those pesky columns
- 使用exchange partition來交換不同schema之間的表
- 記次10g exchange partition很慢的問題
- exchange partition 交換的問題ORA-14130:
- 今天測試了一下update partition table的part key
- Julia語言的一些測試
- oracle partition分割槽_分割槽列為空測試(一)Oracle
- 一些sqll測試題SQL
- 一些簡單的Python測試題Python
- 測試工具培訓的一些建議
- Oracle12c分割槽新特性之TRUNCATEPARTITION和EXCHANGE PARTITION級聯功能Oracle
- 學習自動化測試的一些感悟
- 一些ASM 資料庫的測試和管理ASM資料庫
- 10年老測試工程師的一些心得：結合案例談談迴歸測試和確認測試工程師
- 針對mysql不同binlog模式的一些測試MySql模式
- jmock 進行單元測試的一些疑問Mock
- [20130513]Interval Partition的一些問題.txt
- 移動端遊戲測試一些問答遊戲
- 菜鳥教程 html測試一些問題HTML
- 今天測試oracle stream遇到一些問題Oracle
- 除錯,虛擬環境檢測試的一些新想法除錯
- partition table and partition indexIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- 黑盒測試、白盒測試、單元測試、整合測試、系統測試、驗收測試的區別與聯絡...