Partitioning made easy - Part 3

Oracle has been simplifying and enhancing the  implementation of its partitioning features in every release.

I have listed some of the paritioning enhancements  in Oracle 12c Release in the following posts.

http://mfzahirdba.blogspot.com/2014/12/partitioning-made-easy.html
http://mfzahirdba.blogspot.com/2015/03/partitioning-made-easy-part-2.html

In Oracle 11g , we had auto creation of  partition in range based partitions.
See http://mfzahirdba.blogspot.com/2011/02/interval-partitioning.html

In Oracle 12c Release 2 ,  Oracle has introduced auto creation of partition in list based partitions.
Here is an example. 

SQL> CREATE TABLE t
  2   (
  3   oid int  ,
  4   oname varchar2(200),
  5   otype VARCHAR2(50)
  6   )
  7   partition BY list
  8    (otype)
  9    ( partition p_tab VALUES  ('TABLE'));

Table created.

SQL> insert into t
  2  select object_id ,
  3  object_name ,
  4  object_type from all_objects
  5  where object_type ='TABLE';

132 rows created.

Now , 132 records were inserted into the table t , as we have listed 'TABLE' as the value of the partitioning element.

If we try to insert records that are other than 'TABLE' , then we are greeted with the error. 

SQL> insert into t
  2  select object_id ,
  3  object_name ,
  4  object_type from all_objects
  5  where object_type ='PROCEDURE';

insert into t select object_id , object_name , object_type from all_objects where object_type ='PROCEDURE'
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

In release 2 , if we create the table in the following way , new values o otype will have its own partition. 

Let us create another table and perform the above mentioned steps. 

SQL> CREATE TABLE t1
  2   (
  3   oid int  ,
  4   oname varchar2(200),
  5   otype VARCHAR2(50)
  6   )
  7   partition BY list
  8    (otype) automatic
  9    ( partition p_tab VALUES  ('TABLE'));

Table created.

SQL> insert into t1
  2  select object_id ,
  3  object_name ,
  4  object_type from all_objects
  5  where object_type ='TABLE';

132 rows created.

SQL> insert into t1
  2  select object_id ,
  3  object_name ,
  4  object_type from all_objects
  5  where object_type ='PROCEDURE';

27 rows created.

The second insert created automatically created a partition to insert the new value ( aka , records with otype 'PROCEDURE') and given the partition system defined name . Of course , we can rename this partition to suit our needs later.  

SQL> col table_name format a5
SQL> col partition_name format a25
SQL> col high_value  format a25
SQL> select table_name , partition_name , high_value from user_tab_partitions;

SQL> select table_name , partition_name , high_value from user_tab_partitions;

TABLE PARTITION_NAME            HIGH_VALUE

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

T     P_TAB                     'TABLE'

T1    P_TAB                     'TABLE'

T1    SYS_P212                  'PROCEDURE'

With this enhancement , partitioning is made little bit easier. 

Comments welcome.