|
概述
Partition maintenance operations can be performed on multiple partitions as part of a single partition maintenance operation.
分区维护操作可以在多个分区上执行，作为单个分区维护操作的一部分。
A single partition maintenance operation working on multiple partitions at the same time simplifies application development and leads to more efficient partition maintenance using less system resources.
同时在多个分区上运行的单个分区维护操作简化了应用程序开发，并使用更少的系统资源导致更有效的分区维护。
更多分区表维护信息可参考：http://docs.oracle.com/database/121/VLDBG/GUID-A226B597-BCF1-49E2-8284-739A99D3F9ED.htm#VLDBG1118
实验
下面的例子展示了分区级别的功能。
1） 创建分区表
SQL> col table_name for a10
[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 18 10:49:54 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> DROP TABLE t2 PURGE;
Table dropped.
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')));
Table created.
SQL> col table_name for a10
SQL> col partition_name for a40
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2; 2 3 4
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
2）增加多个分区
SQL> ALTER TABLE t1
ADD
PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'));
Table altered.
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2; 2 3 4
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
T1 PART_2015
T1 PART_2016
T1 PART_2017
T1 PART_2018
3）合并多个子分区到一个分区
SQL> ALTER TABLE t1
MERGE
PARTITIONS part_2015, part_2016, part_2017, part_2018
INTO PARTITION part_2018;
Table altered.
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
T1 PART_2018
4）拆分成多个分区
SQL> ALTER TABLE t1
SPLIT
PARTITION part_2018 INTO (
PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION part_2018
);
Table altered.
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2; 2 3 4
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
T1 PART_2015
T1 PART_2016
T1 PART_2017
T1 PART_2018
5）TRUNCATE多个分区
SQL> ALTER TABLE t1
TRUNCATE
PARTITION part_2014, part_2015, part_2016, part_2017, part_2018; 2 3
Table truncated.
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2; 2 3 4
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
T1 PART_2015
T1 PART_2016
T1 PART_2017
T1 PART_2018
6）删除多个分区
SQL> ALTER TABLE t1
DROP
PARTITION part_2016, part_2017, part_2018; 2 3
Table altered.
SQL> SELECT table_name,
partition_name
FROM user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2; 2 3 4
TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1 PART_2014
T1 PART_2015
参考文档
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT205
http://docs.oracle.com/database/121/VLDBG/GUID-A226B597-BCF1-49E2-8284-739A99D3F9ED.htm#VLDBG1118 | 
转播
