Splitting a Partition Into Multiple Partitions in Oracle 12C & Above
(Doc ID 1482230.1)
Last updated on FEBRUARY 22, 2024
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterGen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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 SPLITPARTITION clause of the ALTER TABLE statement. When splitting multiple partitions, thesegment 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| RANGE partitions |
| LIST partitions |