In Oracle 12c rel.2 a great number of enhancements to the Oracle database Partitioning option have
been introduced. These include:
Automatic List Partitioning: The ability to partition a table based on distinct values in a column
has been around since quite some time, but what is new in Oracle database 12.2 is that we can
initially partition the table based on values that are known and present in the column we would like
to partition the table on. As data is loaded into the table, new partitions are automatically created by
the database if the loaded partition key value does not correspond to any of the existing partitions.
In Oracle Database 12c Release 2, we can now partition a table based on list of values of
multiple columns and not just a single column as in the case of previous versions. This is called
Multi-Column List Partitioning.
Another new feature is Read-Only Partitions. The entire table is not made read-only, but just some
partitions in the table can be made read-only. Maybe we have a requirement to store historical data
and older partitions in the table can be made read-only to prevent any DML operations on such
As DBA’s, one of the common partition maintenance operations we had to carry out quite regularly
was moving partitions – maybe older partitions of the table are moved to a different tablespace
hosted on low cost storage because this data is infrequently accessed. Now in Oracle Database 12.2,
we can combine partition maintenance operations like MOVE PARTITION, MERGE
PARTITION and SPLIT PARTITION with data filtering.
One of the best new 12.2 features is that we can now convert a non-partitioned table into a
partitioned table very easily via the ALTER TABLE command with the MODIFY clause as
well as while concurrent DML operations are being performed on the table. So the conversion of
the table is an online process and with the UPDATE INDEXES clause we can also convert any
indexes on the table into either local or global partitioned indexes as well.
We can also create a Partitioned External Table. We are creating a table based on data hosted
outside the Oracle database and we partition the table which contains this external data for better
optimization of queries which would be really beneficial if we are dealing with very large volumes
of these external data sets.