ITPub博客

首页 > 数据库 > Oracle > exchange partition 实验

exchange partition 实验

原创 Oracle 作者:wzz123snow 时间:2015-08-07 17:03:13 0 删除 编辑
本实验的主要有两个目的:
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-11-13

  • 博文量
    27
  • 访问量
    404620