Splitting a partition of a *-list partitioned table is similar to the description in "Splitting a Partition of a Range-Partitioned Table". No subpartition literal value list can be specified for either of the new partitions. The new partitions inherit the subpartition descriptions from the original partition being split.
The following example splits the q1_1999 partition of the quarterly_regional_sales table:
ALTER TABLE quarterly_regional_sales SPLIT PARTITION q1_1999
AT (TO_DATE('15-Feb-1999','dd-mon-yyyy'))
INTO ( PARTITION q1_1999_jan_feb
TABLESPACE ts1,
PARTITION q1_1999_feb_mar
STORAGE (INITIAL 8M) TABLESPACE ts2)
PARALLEL 5;
This operation splits the partition q1_1999 into two resulting partitions: q1_1999_jan_feb and q1_1999_feb_mar. Both partitions inherit their subpartition descriptions from the original partition. The individual partitions have new physical attributes, including tablespaces, specified at the partition level. These new attributes become the default attributes of the new partitions. This operation is run with parallelism of degree 5.
The ALTER TABLE SPLIT PARTITION statement provides no means of specifically naming subpartitions resulting from the split of a partition in a composite partitioned table. However, for those subpartitions in the parent partition with names of the form partition name_subpartition name, the database generates corresponding names in the newly created subpartitions using the new partition names. All other subpartitions are assigned system generated names of the form SYS_SUBPn. System generated names are also assigned for the subpartitions of any partition resulting from the split for which a name is not specified. Unnamed partitions are assigned a system generated partition name of the form SYS_Pn.
The following query displays the subpartition names resulting from the previous split partition operation on table quarterly_regional_sales. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation in "About Creating Composite Range-List Partitioned Tables".
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='QUARTERLY_REGIONAL_SALES' ORDER BY PARTITION_NAME; PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME -------------------- ------------------------------ --------------- Q1_1999_FEB_MAR Q1_1999_FEB_MAR_WEST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_NORTHEAST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTHEAST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_NORTHCENTRAL TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTHCENTRAL TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTH TS2 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_WEST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_NORTHEAST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTHEAST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_NORTHCENTRAL TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTHCENTRAL TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTH TS1 Q1_2000 Q1_2000_NORTHWEST TS3 Q1_2000 Q1_2000_SOUTHWEST TS3 Q1_2000 Q1_2000_NORTHEAST TS3 Q1_2000 Q1_2000_SOUTHEAST TS3 Q1_2000 Q1_2000_NORTHCENTRAL TS3 Q1_2000 Q1_2000_SOUTHCENTRAL TS3 Q2_1999 Q2_1999_NORTHWEST TS4 Q2_1999 Q2_1999_SOUTHWEST TS4 Q2_1999 Q2_1999_NORTHEAST TS4 Q2_1999 Q2_1999_SOUTHEAST TS4 Q2_1999 Q2_1999_NORTHCENTRAL TS4 Q2_1999 Q2_1999_SOUTHCENTRAL TS4 Q3_1999 Q3_1999_NORTHWEST TS4 Q3_1999 Q3_1999_SOUTHWEST TS4 Q3_1999 Q3_1999_NORTHEAST TS4 Q3_1999 Q3_1999_SOUTHEAST TS4 Q3_1999 Q3_1999_NORTHCENTRAL TS4 Q3_1999 Q3_1999_SOUTHCENTRAL TS4 Q4_1999 Q4_1999_NORTHWEST TS4 Q4_1999 Q4_1999_SOUTHWEST TS4 Q4_1999 Q4_1999_NORTHEAST TS4 Q4_1999 Q4_1999_SOUTHEAST TS4 Q4_1999 Q4_1999_NORTHCENTRAL TS4 Q4_1999 Q4_1999_SOUTHCENTRAL TS4 36 rows selected.