oracle list 分区详解,Oracle 12.2 新特性：自动列表分区(Auto-List Partitioning)

The database automatically creates a separate (new) partition for every distinct partition key value of the table.

Auto-list partitioning removes the management burden from the DBAs to manually maintain a list of partitioned tables for a large number of distinct key values that require individual partitions. It also automatically copes with the unplanned partition key values without the need of a DEFAULT partition.

通过以下测试来简单验证一下这个特性的表征,如果是常规的列表分区，在分区缺失时会遇到ORA-14400错误：

SQL> CREATE TABLE enmotech (

2 PartID integer not null,

3 CretTm date not null,

4 PartCD varchar2(2) not null

5 ) partition by list (partcd) (

6 partition pBJ values ('BJ'),

7 partition pCD values ('CD'),

8 partition pGZ values ('GZ'),

9 partition pSH values ('SH')

10 );

Table created.

SQL> insert into enmotech values (1, sysdate, 'KM');

insert into enmotech values (1, sysdate, 'KM')

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

当设置了automatic关键字之后，分区变更为自动管理：

drop table enmotech purge;

CREATE TABLE enmotech (

PartIDintegernot null,

CretTmdatenot null,

PartCDvarchar2(2)not null

) partition by list (partcd) automatic (

partition pBJ values ('BJ'),

partition pCD values ('CD'),

partition pGZ values ('GZ'),

partition pSH values ('SH')

);

当插入一条未定义的分区数据时，新的分区被自动创建：

SQL> insert into enmotech values (1, sysdate, 'KM');

1 row created.

SQL> select partition_name from user_tab_partitions

2 where table_name = 'ENMOTECH';

PARTITION_NAME

----------------------------------------------------

PBJ

PCD

PGZ

PSH

SYS_P290

如果这个自动分片的分区名不符合你的命名规则，可以通过DDL语句去修改变更：

SQL> alter table enmotech rename partition SYS_P290 to pKM;

Table altered.

SQL> select partition_name from user_tab_partitions

2 where table_name = 'ENMOTECH';

PARTITION_NAME

---------------------------------------------------

PBJ

PCD

PGZ

PKM

PSH

对于已有的分区定义，可以通过关键字 automatic 和 manual 来进行分区定义的调整：

alter table PEOPLE set partitioning automatic;

alter table PEOPLE set partitioning manual;

这是Oracle Database 12.2 分区特性的众多增强之一。