Use ALTER TABLE SET SUBPARTITION TEMPLATE to modify the subpartition template for an existing partition. After you set a new subpartition template, partitions that you add subsequently will have the new subpartition design. Existing partitions are not modified.

ALTER TABLE sales SET SUBPARTITION TEMPLATE

( SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

SUBPARTITION africa VALUES ('africa')

DEFAULT SUBPARTITION other );

With this example template, when you next add a date-range partition of the table sales, it will include the new regional list subpartition for Africa. For example, the following command would create the subpartitions usa, asia, europe, africa, and a default partition named other:

ALTER TABLE sales ADD PARTITION sales_prt_3

START ('2009-03-01') INCLUSIVE

END ('2009-04-01') EXCLUSIVE );

If you need to remove a subpartition template, use SET SUBPARTITION TEMPLATE with empty parentheses. For example, to completely clear the subpartition template used in the above examples:

ALTER TABLE sales SET SUBPARTITION TEMPLATE ()