The following example will divide one of the partitions in the range-partitioned sales table into new partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the contents of the partition between them.
The sales table contains partition q1_2012 and three subpartitions europe, americas, and asia.
The following command splits the q1_2012 partition into two partitions named q1_2012_p1 and q1_2012_p2.
A SELECT statement confirms that the same number of subpartitions is created in the newly created partitions q1_2012_p1 and q1_2012_p2 with system-generated names.
Example - Splitting a Partition with SUBPARTITIONS num...
The following example will divide one of the partitions in the list-partitioned sales table into new partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the contents of the partition between them. The SUBPARTITIONS clause lets you add a specified number of subpartitions. If no SUBPARTITIONS clause is specified then the new partitions inherit the default number of subpartitions. The sales table is created with the statement:
The table definition creates three partitions (europe, asia, and americas) each containing two subpartitions.
The following command splits the americas partition into two partitions named partition_us and partition_canada.
A SELECT statement confirms that the americas partition is split into partition_us and partition_canada. The partition_us contains five subpartitions and partition_canada contains default two subpartitions and assigned system-generated names.
Example - Splitting a Partition with SUBPARTITIONS num...STORE IN
The following example will divide the europe partition of the list-partitioned sales table into two partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the partition's contents. The SUBPARTITIONS clause lets you add a specified number of subpartitions. Use the following command to create the sales table:
The sales table contains three partitions (europe, asia, and americas) each containing four subpartitions with system-generated names assigned to them.
The following command splits the europe partition into two partitions named france and italy.
A SELECT statement confirms that the europe partition is split into two partitions. The partition france contains ten subpartitions that are stored in the tablespace ts1 and partition italy contains four subpartitions as in the original partition europe.