【实验】【PARTITION】RANGE分区表合并分区

1.创建测试用range分区表
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
  2  partition by range(id)(
  3  partition t_range_p1   values less than (10)       tablespace tbs_part01,
  4  partition t_range_p2   values less than (20)       tablespace tbs_part02,
  5  partition t_range_p3   values less than (30)       tablespace tbs_part03,
  6  partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);

Table created.

2.查询分区情况
sec@ora10g> col TABLE_NAME for a20
sec@ora10g> col partition_name for a20
sec@ora10g> col HIGH_VALUE for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE    T_RANGE_P1           10         TBS_PART01
T_PARTITION_RANGE    T_RANGE_P2           20         TBS_PART02
T_PARTITION_RANGE    T_RANGE_P3           30         TBS_PART03
T_PARTITION_RANGE    T_RANGE_PMAX         MAXVALUE   TBS_PART04

3.每个分区各插入一条数据
sec@ora10g> insert into T_PARTITION_RANGE values (9,'secooler01');

1 row created.

sec@ora10g> insert into T_PARTITION_RANGE values (19,'secooler02');

1 row created.

sec@ora10g> insert into T_PARTITION_RANGE values (29,'secooler03');

1 row created.

sec@ora10g> insert into T_PARTITION_RANGE values (39,'secooler04');

1 row created.

sec@ora10g> commit;

Commit complete.

4.确认每个分区包含的数据
sec@ora10g> select * from T_PARTITION_RANGE partition(T_RANGE_P1);

        ID NAME
---------- --------------------------------------------------
         9 secooler01

sec@ora10g> select * from T_PARTITION_RANGE partition(T_RANGE_P2);

        ID NAME
---------- --------------------------------------------------
        19 secooler02

sec@ora10g> select * from T_PARTITION_RANGE partition(T_RANGE_P3);

        ID NAME
---------- --------------------------------------------------
        29 secooler03

sec@ora10g> select * from T_PARTITION_RANGE partition(T_RANGE_PMAX);

        ID NAME
---------- --------------------------------------------------
        39 secooler04

5.开始合并分区--将前两个分区t_range_p1,t_range_p2合并到新分区t_range_pmerge中
sec@ora10g> alter table t_partition_range merge partitions t_range_p1,t_range_p2 into partition t_range_pmerge tablespace tbs_part04;

Table altered.

6.再一次确认分区情况和每一个分区内存放的数据
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE    T_RANGE_PMERGE       20         TBS_PART04
T_PARTITION_RANGE    T_RANGE_P3           30         TBS_PART03
T_PARTITION_RANGE    T_RANGE_PMAX         MAXVALUE   TBS_PART04

sec@ora10g> select * from t_partition_range partition(T_RANGE_PMERGE);

        ID NAME
---------- --------------------------------------------------
         9 secooler01
        19 secooler02

sec@ora10g> select * from t_partition_range partition(T_RANGE_P3);

        ID NAME
---------- --------------------------------------------------
        29 secooler03

sec@ora10g> select * from t_partition_range partition(T_RANGE_PMAX);

        ID NAME
---------- --------------------------------------------------
        39 secooler04

7.OK,合并分区成功

8.向低一级别的分区合并是不被允许的(较大的值不能插入到较小值分区中)
sec@ora10g> alter table t_partition_range merge partitions t_range_p1,t_range_p2 into partition t_range_p1;
alter table t_partition_range merge partitions t_range_p1,t_range_p2 into partition t_range_p1 tablespace tbs_part04
                                               *
ERROR at line 1:
ORA-14275: cannot reuse lower-bound partition as resulting partition

9.向高一级别的分区合并没有问题
sec@ora10g> alter table t_partition_range merge partitions t_range_p1,t_range_p2 into partition t_range_p2;

Table altered.


-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-609245/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-609245/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值