分区表管理：合并分区
有时候个别分区数据量少或者其他原因，需要对一些分区进行合并成一个分区，
以下就是合并分区的演示过程：
----合并分区：
---查看分区：
sys@PROD>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 from DBA_TAB_PARTITIONS
3 where table_name in ('PART_TEST','p_TEST');
TABLE_OWNER TABLE_NAME PARTITION_ TABLESPACE_NAME
--------------- --------------- ---------- ---------------
SYS PART_TEST PART_01 SYSTEM
SYS PART_TEST SYS_P61 SYSTEM
SYS PART_TEST SYS_P62 SYSTEM
SYS PART_TEST SYS_P63 SYSTEM
SYS PART_TEST SYS_P64 SYSTEM
SYS PART_TEST SYS_P65 SYSTEM
6 rows selected.
---查看分区PART_01数据：
sys@PROD>select * from part_test partition (PART_01);
no rows selected
#没有数据。
---插入测试数据：
insert into part_test values(121,'11-JAN-2015');
insert into part_test values(122,'12-JAN-2015');
insert into part_test values(123,'13-JAN-2015');
insert into part_test values(124,'14-JAN-2015');
insert into part_test values(125,'15-JAN-2015');
Commit;
---再次查看分区PART_01数据：
sys@PROD>select * from part_test partition (part_01);
ID CREATED
---------- ---------
121 11-JAN-15
122 12-JAN-15
123 13-JAN-15
124 14-JAN-15
125 15-JAN-15
---再次插入两条测试记录：
insert into part_test values(221,'11-JAN-2016');
insert into part_test values(222,'12-JAN-2016');
commit;
---新增两条记录后再次查看分区的情况：
sys@PROD>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 from DBA_TAB_PARTITIONS
3 where table_name in ('PART_TEST','p_TEST');
TABLE_OWNER TABLE_NAME PARTITION_ TABLESPACE_NAME
--------------- --------------- ---------- ---------------
SYS PART_TEST PART_01 SYSTEM
SYS PART_TEST SYS_P61 SYSTEM
SYS PART_TEST SYS_P62 SYSTEM
SYS PART_TEST SYS_P63 SYSTEM
SYS PART_TEST SYS_P64 SYSTEM
SYS PART_TEST SYS_P65 SYSTEM
SYS PART_TEST SYS_P66 SYSTEM
7 rows selected.
#这次查看共有7个分区，比上一次查询多了一个分区SYS_P66。
---查看分区SYS_P66数据：
sys@PROD>select * from part_test partition (sys_p66);
ID CREATED
---------- ---------
221 11-JAN-16
222 12-JAN-16
---尝试将分区PART_01与分区SYS_P66 合并为SYS_P60分区：
sys@PROD>alter table part_test merge partitions
2 part_01,sys_p66 into partition sys_p60;
Table altered.
#两个分区已经合并完成。
---再次查看表的分区情况：
sys@PROD>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 from DBA_TAB_PARTITIONS
3 where table_name in ('PART_TEST','p_TEST');
TABLE_OWNER TABLE_NAME PARTITION_ TABLESPACE_NAME
--------------- --------------- ---------- ---------------
SYS PART_TEST SYS_P60 SYSTEM
SYS PART_TEST SYS_P61 SYSTEM
SYS PART_TEST SYS_P62 SYSTEM
SYS PART_TEST SYS_P63 SYSTEM
SYS PART_TEST SYS_P64 SYSTEM
SYS PART_TEST SYS_P65 SYSTEM
6 rows selected.
#合并分区成功，原来的分区PART_01与分区SYS_P66合并为一个分区SYS_P60。
---查看新分区的数据记录：
sys@PROD>select * from part_test partition (sys_p60);
ID CREATED
---------- ---------
121 11-JAN-15
122 12-JAN-15
123 13-JAN-15
124 14-JAN-15
125 15-JAN-15
221 11-JAN-16
222 12-JAN-16
7 rows selected.
#新分区的数据为原来两个分区的并集记录。
--分区后可能会导致分区表中原有的索引失效，需要重建，这里不做叙述。
有兴趣的朋友们可以自己进行测试。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2129755/ ，如需转载，请注明出处，否则将追究法律责任。
