oracle 12C 查多个分区,Oracle 12c 新特性 --- 对多个分区进行维护操作

概述https://www.cndba.cn/leo1990/article/2140

https://www.cndba.cn/leo1990/article/2140

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

https://www.cndba.cn/leo1990/article/2140

https://www.cndba.cn/leo1990/article/2140

实验

https://www.cndba.cn/leo1990/article/2140

下面的例子展示了分区级别的功能。

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

https://www.cndba.cn/leo1990/article/2140https://www.cndba.cn/leo1990/article/2140

参考文档

http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT205

https://www.cndba.cn/leo1990/article/2140https://www.cndba.cn/leo1990/article/2140

版权声明：本文为博主原创文章，未经博主允许不得转载。