这里说的拆分分区,只对maxvalue分区的分区表来说的,实质也是添加分区。
以下是测试过程:
----拆分分区 添加分区与交换分区:
---拆分分区:(拆分分区的前提条件是有一个maxvalue分区)
suxing@PROD>SET LINES 120
suxing@PROD>COL TABLE_NAME FOR A15
suxing@PROD>COL PARTITION_NAME FOR A15
suxing@PROD>COL TABLESPACE_NAME FOR A30
suxing@PROD>SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
--------------- --------------- ------------------------------
P_TEST P_10 MYSPACE
P_TEST P_20 USERS
P_TEST P_30 MYSPACE
P_TEST P_40 USERS
---查看表中分区p_40的数据记录:
suxing@PROD>select * from p_test partition (p_40);
ID NAME CREATED
---------- ---------- -------------------
44 susu 2016-12-05 09:53:59
55 susu 2016-12-05 09:54:06
---尝试拆分分区p_40:
suxing@PROD>alter table p_test split partition p_40 at(50)
2 into (partition p_400,partition p_max);
Table altered.
#分区p_40已经拆分完成。
---再次查看分区情况:
suxing@PROD>SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
--------------- --------------- ------------------------------
P_TEST P_400 USERS
P_TEST P_10 MYSPACE
P_TEST P_20 USERS
P_TEST P_30 MYSPACE
P_TEST P_MAX USERS
#分区已经成功。
---查看新分两个分区的记录:
suxing@PROD>select * from p_test partition (p_400);
ID NAME CREATED
---------- ---------- -------------------
44 susu 2016-12-05 09:53:59
suxing@PROD>select * from p_test partition (p_MAX);
ID NAME CREATED
---------- ---------- -------------------
55 susu 2016-12-05 09:54:06
#拆分前,最大的分区共有两条数据记录,拆分后,两个新分区各得一条记录。
#拆分分区的前提条件是分区表有maxvalue分区,如果该分区表的(除自动分区表)没有该分区的,只能使用
alter table table_name add partition partition_name 来添加新分区。
而有maxvalue分区的一般通过拆分方式添加新分区。
---尝试直接add方式给没有maxvalue分区的分区表添加新分区:
suxing@PROD>alter table p_test add partition p_500
2 values less than (60) tablespace myspace;
alter table p_test add partition p_500
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
#报错,不能添加新分区。
----交换分区:
---创建另外的普通测试表:
suxing@PROD>create table pp_test(
2 id number(2),
3 name varchar2(10),
4 created date);
Table created.
---往测试表pp_test插入3条记录:
insert into pp_test values(41,'susu',sysdate);
insert into pp_test values(42,'susu',sysdate);
insert into pp_test values(43,'susu',sysdate);
suxing@PROD>commit;
---查看测试表pp_test的数据记录:
suxing@PROD>select * from pp_test;
ID NAME CREATED
---------- ---------- -------------------
41 susu 2016-12-05 16:26:19
42 susu 2016-12-05 16:26:48
43 susu 2016-12-05 16:26:50
---查看分区表分区p_400的记录:
suxing@PROD>select * from p_test partition (p_400);
ID NAME CREATED
---------- ---------- -------------------
44 susu 2016-12-05 09:53:59
---进行分区交换:
suxing@PROD>alter table p_test exchange partition p_400 with table pp_test;
Table altered.
#交换完成。
---再次查看测试表pp_test记录:
suxing@PROD>select * from pp_test;
ID NAME CREATED
---------- ---------- -------------------
44 susu 2016-12-05 09:53:59
---再次查看分区表分区p_400的记录:
suxing@PROD>select * from p_test partition (p_400);
ID NAME CREATED
---------- ---------- -------------------
41 susu 2016-12-05 16:26:19
42 susu 2016-12-05 16:26:48
43 susu 2016-12-05 16:26:50
#分区交换成功。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2129756/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2129756/