exchange partition 實驗

本實驗的主要有兩個目的：
1. 測試分割槽交換的命令
2. 模擬在交換分割槽的時候出現ORA-14099的錯誤，利用insert來建立分割槽

--首先建立兩張表結構相同的分割槽表T1，T2，再建立一張中間表T1_MIDDLE
SQL> create table t1 (id int,name varchar2(10),hire_date date)
  2  partition by range(hire_date) interval(NUMTODSINTERVAL(7,'DAY'))
  3  (partition p1 values less than (to_date('05-Jan-2015','dd-mon-yyyy')));

Table created.

SQL>
SQL> create table t2 (id int,name varchar2(10),hire_date date)
  2  partition by range(hire_date) interval(NUMTODSINTERVAL(7,'DAY'))
  3  (partition p1 values less than (to_date('05-Jan-2015','dd-mon-yyyy')));

Table created.

SQL>
SQL> create table t1_middle (id int,name varchar2(10),hire_date date);

Table created.

--目前T1表只有一個預設分割槽P1

SQL> select partition_name from dba_tab_partitions where table_name='T1';

PARTITION_NAME
------------------------------
P1

--往T1表插入資料，構建資料
SQL> insert into t1 values(1,'a','31-JUL-15');

1 row created.

SQL> insert into t1 values(2,'b','30-JUL-15');

1 row created.

SQL> insert into t1 values(3,'c','29-JUL-15');

1 row created.

SQL> insert into t1 values(4,'d','14-JUL-15');

1 row created.

SQL> insert into t1 values(5,'e','4-AUG-15');

1 row created.

SQL> insert into t1 values(6,'F','6-AUG-15');

1 row created.

SQL> insert into t1 values(7,'F','7-AUG-15');

1 row created.

SQL> insert into t1 values(8,'G','5-AUG-15');

1 row created.

SQL> commit;

Commit complete.

--此時查詢一下表T1有哪些分割槽，由於插入資料，新增了幾個分割槽
SQL> select partition_name from dba_tab_partitions where table_name='T1';

PARTITION_NAME
------------------------------
P1
SYS_P50
SYS_P49
SYS_P51

--查詢T1表每個分割槽的資料

SQL> select COUNT(1) from t1 partition (SYS_P49);

  COUNT(1)
----------
         3

SQL> select COUNT(1) from t1 partition (SYS_P50);

  COUNT(1)
----------
         1

SQL> select COUNT(1) from t1 partition (SYS_P51);

  COUNT(1)
----------
         4

SQL> SELECT COUNT(1) FROM T1_MIDDLE;

  COUNT(1)
----------
         0

SQL> SELECT COUNT(1) FROM T1;

  COUNT(1)
----------
         8

--接下來我們嘗試交換分割槽，將SYS_P51這個分割槽的資料交換到臨時表T1_MIDDLE中，交換後SYS_P51中的4條資料沒有了，而中間表T1_MIDDLE有了4條資料
SQL> alter table t1 exchange partition SYS_P51 WITH table t1_middle update global indexes;

Table altered.

SQL> SELECT COUNT(1) FROM T1;

  COUNT(1)
----------
         4

SQL> select COUNT(1) from t1 partition (SYS_P51);

  COUNT(1)
----------
         0

SQL> SELECT COUNT(1) FROM T1_MIDDLE;

  COUNT(1)
----------
         4
        
--我們看下T2表的分割槽情況
SQL> select partition_name  from dba_tab_partitions where table_name='T2';

PARTITION_NAME
------------------------------
P1

SQL> select count(1) from t2;

  COUNT(1)
----------
         0
        
--我們嘗試把中間表T1_MIDDLE的資料交換到目標表T2中，在交換的過程中遇到了ORA-14099的錯誤，原因是沒有指定的分割槽
SQL> alter table t2 exchange partition p1 with table t1_middle update global indexes;
alter table t2 exchange partition p1 with table t1_middle update global indexes
                                                *
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition

--我們可以利用insert 建立分割槽，然後馬上進行rollback
SQL> insert into t2 select * from t1_middle where rownum=1
  2  ;

1 row created.

SQL> rollback;

Rollback complete.

--再查下分割槽表T2的分割槽，增加了一個SYS_P52的分割槽

SQL> select partition_name  from dba_tab_partitions where table_name='T2';

PARTITION_NAME
------------------------------
P1
SYS_P52


--此時再進行分割槽交換，分割槽交換後，中間表T1_MIDDLE的資料沒有了，交換到了目標表T2中
SQL> alter table t2 exchange partition SYS_P52 with table t1_middle update global indexes;

Table altered.

SQL> SELECT COUNT(1) FROM T1_MIDDLE;

  COUNT(1)
----------
         0

SQL> select count(1) from t2;

  COUNT(1)
----------
         4

SQL> select count(1) from t1;

  COUNT(1)
----------
         4

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26084062/viewspace-1764924/，如需轉載，請註明出處，否則將追究法律責任。