Partition => 12cR2 New Feature "Automatic LIST Partition"

Automatic list partitioning was introduced in Oracle Database 12c Release 2 (12.2) to handle the new partitions according to the distinct values of partition key.


CREATE TABLE employees ( employee_id NUMBER(4) ,first_name VARCHAR2(30) ,last_name VARCHAR2(30) ,email VARCHAR2(30) ,phone_number VARCHAR2(20) ,hire_date DATE ,salary NUMBER(6,0) ,commission_pct NUMBER(3,2) ,manager_id NUMBER(4) ,department_id NUMBER(4) ) PARTITION BY LIST (department_id) AUTOMATIC ( PARTITION P_10 VALUES (10) );

It prevents getting the following error when a new record (whose partition key values doesnt exist in the list) came.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

There is a new column on the (user/dba)_part_tables to check if the table is autmatic list partitioned.

SELECT table_name,
       autolist
FROM   user_part_tables;

TABLE_NAME                     AUTOLIST
------------------------------ --------
EMPLOYEES                         YES

SQL>