在以往对oracle分区表做drop、truncate等分区操作时，每次只能对一个分区进行操作，从12.1.0.1.0开始，可以在一条sql中对多个分区做以上操作。
测试示例如下：
1. 创建测试表
2. drop多个分区
可以删除多个分区，但是不能删除全部分区
如果存在全局索引或则本地索引，在操作完成后必需重建Global and local indexes 索引，
除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句
如：alter table mulpartest drop partitions p1,p2,p3,p4 update global indexes;
3. Truncate多个分区
如果存在全局索引或则本地索引，在操作完成后必需重建Global and local indexes 索引，
除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句
如：alter table mulpartest truncate partitions p5,p6,p7,p8 update global indexes;
4. ADD多个分区
5. 合并多个分区
6. 分割成多个分区
测试示例如下：
1. 创建测试表
SQL>
SQL> create table mulpartest (id number, name varchar2(40))
2 partition by range (id)
3 (partition p1 values less than (1000),
4 partition p2 values less than (2000),
5 partition p3 values less than (3000),
6 partition p4 values less than (4000),
7 partition p5 values less than (5000),
8 partition p6 values less than (6000),
9 partition p7 values less than (7000),
10 partition p8 values less than (8000),
11 partition p9 values less than (9000),
12 partition po values less than (maxvalue));
Table created.
SQL>
SQL>
SQL> insert into mulpartest select object_id,object_name from dba_objects where object_id <= 20000;
19524 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ --------------------------------------------------
HCN MULPARTEST P1 1 1000
HCN MULPARTEST P2 2 2000
HCN MULPARTEST P3 3 3000
HCN MULPARTEST P4 4 4000
HCN MULPARTEST P5 5 5000
HCN MULPARTEST P6 6 6000
HCN MULPARTEST P7 7 7000
HCN MULPARTEST P8 8 8000
HCN MULPARTEST P9 9 9000
HCN MULPARTEST PO 10 MAXVALUE
10 rows selected.
SQL>
2. drop多个分区
SQL>
SQL> alter table mulpartest drop partitions p1,p2,p3,p4;
Table altered.
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ --------------------------------------------------
HCN MULPARTEST P5 1 5000
HCN MULPARTEST P6 2 6000
HCN MULPARTEST P7 3 7000
HCN MULPARTEST P8 4 8000
HCN MULPARTEST P9 5 9000
HCN MULPARTEST PO 6 MAXVALUE
6 rows selected.
SQL>
SQL> alter table mulpartest drop partitions p5,p6,p7,p8,p9,po;
alter table mulpartest drop partitions p5,p6,p7,p8,p9,po
*
ERROR at line 1:
ORA-14331: cannot drop all [sub]partitions of a partitioned table
SQL>
可以删除多个分区，但是不能删除全部分区
如果存在全局索引或则本地索引，在操作完成后必需重建Global and local indexes 索引，
除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句
如：alter table mulpartest drop partitions p1,p2,p3,p4 update global indexes;
3. Truncate多个分区
SQL>
SQL> select count(*) from mulpartest;
COUNT(*)
----------
15528
SQL>
SQL>
SQL> alter table mulpartest truncate partitions p5,p6,p7,p8;
Table truncated.
SQL>
SQL> select count(*) from mulpartest;
COUNT(*)
----------
11532
SQL>
SQL>
如果存在全局索引或则本地索引，在操作完成后必需重建Global and local indexes 索引，
除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句
如：alter table mulpartest truncate partitions p5,p6,p7,p8 update global indexes;
4. ADD多个分区
SQL>
SQL> ALTER TABLE mulpartest ADD partition p1 values less than (1000),
2 partition p2 values less than (2000),
3 partition p3 values less than (3000),
4 partition p4 values less than (4000);
ALTER TABLE mulpartest ADD partition p1 values less than (1000),
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL>
SQL>
SQL>
SQL> select max(id) from MULPARTEST;
MAX(ID)
----------
20000
SQL>
---在给范围分区的分区表做add多个分区操作时需要满足：
---1. 新增分区的值要大于当前最后一个分区的值
---2. 不能存在MAXVALUE分区
SQL>
SQL> alter table mulpartest drop partition po;
Table altered.
SQL>
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ --------------------------------------------------
HCN MULPARTEST P5 1 5000
HCN MULPARTEST P6 2 6000
HCN MULPARTEST P7 3 7000
HCN MULPARTEST P8 4 8000
HCN MULPARTEST P9 5 9000
SQL>
SQL>
SQL>
SQL> ALTER TABLE mulpartest ADD partition p10 values less than (10000),
2 partition p11 values less than (11000);
Table altered.
SQL>
SQL>
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ --------------------------------------------------
HCN MULPARTEST P5 1 5000
HCN MULPARTEST P6 2 6000
HCN MULPARTEST P7 3 7000
HCN MULPARTEST P8 4 8000
HCN MULPARTEST P9 5 9000
HCN MULPARTEST P10 6 10000
HCN MULPARTEST P11 7 11000
7 rows selected.
SQL>
5. 合并多个分区
SQL>
SQL> ALTER TABLE mulpartest MERGE PARTITIONS p5, p6, p7, p8 INTO PARTITION p9;
ALTER TABLE mulpartest MERGE PARTITIONS p5, p6, p7, p8 INTO PARTITION p9
*
ERROR at line 1:
ORA-14012: resulting partition name conflicts with that of an existing partition
----不能merge到已经存在的一个分区
SQL>
SQL> ALTER TABLE mulpartest MERGE PARTITIONS p5, p6, p7, p8 INTO PARTITION p0;
Table altered.
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ --------------------------------------------------
HCN MULPARTEST P0 1 8000
HCN MULPARTEST P9 2 9000
HCN MULPARTEST P10 3 10000
HCN MULPARTEST P11 4 11000
SQL>
6. 分割成多个分区
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ ---------------
HCN MULPARTEST P0 1 8000
HCN MULPARTEST P9 2 9000
HCN MULPARTEST P10 3 10000
HCN MULPARTEST P11 4 11000
SQL>
SQL>
SQL>
SQL> ALTER TABLE MULPARTEST SPLIT PARTITION p0 INTO
2 (PARTITION p1 VALUES LESS THAN (1000),
3 PARTITION p2 VALUES LESS THAN (2000),
4 PARTITION p3 VALUES LESS THAN (3000),
5 PARTITION p4 VALUES LESS THAN (4000),
6 PARTITION p5);
Table altered.
SQL>
SQL>
SQL> select table_owner,
2 table_name,
3 partition_name,
4 partition_position,
5 high_value
6 from dba_tab_partitions
7 where table_name = 'MULPARTEST'
8 and table_OWNER = 'HCN';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- -------------------- -------------------- ------------------ ---------------
HCN MULPARTEST P1 1 1000
HCN MULPARTEST P2 2 2000
HCN MULPARTEST P3 3 3000
HCN MULPARTEST P4 4 4000
HCN MULPARTEST P5 5 8000
HCN MULPARTEST P9 6 9000
HCN MULPARTEST P10 7 10000
HCN MULPARTEST P11 8 11000
8 rows selected.
SQL>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
