Splitting a partition of a *-range partitioned table is similar to the description in "Splitting a Partition of a Range-Partitioned Table". No subpartition range values 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 May 2007 interval partition of the interval-range partitioned
ALTER TABLE orders SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy')) AT (TO_DATE('15-MAY-2007','dd-MON-yyyy')) INTO (PARTITION p_fh_may07,PARTITION p_sh_may2007);
This operation splits the interval partition
FOR('01-MAY-2007') into two resulting partitions:
p_sh_may_2007. Both partitions inherit their subpartition descriptions from the original partition. Any interval partitions before the June 2007 partition have been converted into range partitions, as described in "Merging Interval Partitions".
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
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
n. 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
The following query displays the subpartition names and high values resulting from the previous split partition operation on table
orders. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation.
BREAK ON partition_name SELECT partition_name, subpartition_name, high_value FROM user_tab_subpartitions WHERE table_name = 'ORCERS' ORDER BY partition_name, subpartition_position; PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE ------------------------- ------------------------------ --------------- P_BEFORE_2007 P_BEFORE_2007_P_SMALL 1000 P_BEFORE_2007_P_MEDIUM 10000 P_BEFORE_2007_P_LARGE 100000 P_BEFORE_2007_P_EXTRAORDINARY MAXVALUE P_FH_MAY07 SYS_SUBP2985 1000 SYS_SUBP2986 10000 SYS_SUBP2987 100000 SYS_SUBP2988 MAXVALUE P_PRE_MAY_2007 P_PRE_MAY_2007_P_SMALL 1000 P_PRE_MAY_2007_P_MEDIUM 10000 P_PRE_MAY_2007_P_LARGE 100000 P_PRE_MAY_2007_P_EXTRAORDINARY MAXVALUE P_SH_MAY2007 SYS_SUBP2989 1000 SYS_SUBP2990 10000 SYS_SUBP2991 100000 SYS_SUBP2992 MAXVALUE