Splitting a Partition Into Multiple Partitions in Oracle 12C (Doc ID 1482230.1)

Last updated on FEBRUARY 16, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
Oracle 12C supports splitting a partition into more than 2 partitions.

You can redistribute the contents of one table partition into multiple partitions with the SPLIT
PARTITION clause of the ALTER TABLE statement. When splitting multiple partitions, the
segment associated with the current partition is discarded. Each new partition obtains a new
segment and inherits all of the unspecified physical attributes from the current source partition.

You can use this new extended split syntax to specify a list of new partition descriptions similar to
the create partitioned table SQL statements, instead of using the AT or VALUES clauses.
Additionally, the Range or List values clause for the last new partition description is derived
based on the high bound of the source partition and the bound values specified for the first (N-1)
new partitions resulting from the split.

When splitting a DEFAULT List partition or a MAXVALUE Range partition into multiple partitions, the first
(N-1) new partitions are created using the literal value lists or high bound values specified, while the Nth
new partition resulting from the split have the DEFAULT value or MAXVALUE. The
SPLIT_TABLE_SUBPARTITION clause is extended similarly to allow split of a Range or List subpartition
into N new subpartitions.

Goal

 To demonstrate new feature in 12c that makes possible to split an Oracle table partition or subpartition into multiple partitions/subpartitions with one statement.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms