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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1764924/