10.3.11 ALTER TABLE… DROP SUBPARTITION

Table of Contents Previous Next



Use the ALTER TABLEDROP SUBPARTITION command to drop a subpartition definition, and the data stored in that subpartition. The syntax is:
ALTER TABLE table_name DROP SUBPARTITION subpartition_name;
The ALTER TABLE… DROP SUBPARTITION command deletes a subpartition, and the data stored in that subpartition. To use the DROP SUBPARTITION clause, you must be the owner of the partitioning root, a member of a group that owns the table, or have superuser or administrative privileges.
The example that follows deletes a subpartition of the sales table. Use the following command to create the sales table:
)
PARTITION BY RANGE(date)
SUBPARTITION BY LIST
(country)
(
PARTITION
first_half_2012 VALUES LESS THAN('01-JUL-2012')
(
SUBPARTITION europe VALUES ('ITALY', 'FRANCE'),
SUBPARTITION americas VALUES ('CANADA', 'US'),
SUBPARTITION asia VALUES ('PAKISTAN', 'INDIA')
PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
);
Querying the ALL_TAB_SUBPARTITIONS view displays the subpartition names:
To delete the americas subpartition from the sales table, invoke the following command:
Querying the ALL_TAB_SUBPARTITIONS view demonstrates that the subpartition has been successfully deleted:


Table of Contents Previous Next