3.5.10 ALTER TABLE… DROP PARTITION

Table of Contents Previous Next


3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.10 ALTER TABLE… DROP PARTITION

Use the ALTER TABLEDROP PARTITION command to delete a partition definition, and the data stored in that partition. The syntax is:
ALTER TABLE table_name DROP PARTITION partition_name;
The ALTER TABLE… DROP PARTITION command deletes a partition and any data stored on that partition. The ALTER TABLEDROP PARTITION command can drop partitions of a LIST or RANGE partitioned table; please note that this command does not work on a HASH partitioned table. When you delete a partition, any subpartitions (of that partition) are deleted as well.
To use the DROP PARTITION clause, you must be the owner of the partitioning root, a member of a group that owns the table, or have database superuser or administrative privileges.
The example that follows deletes a partition of the sales table. Use the following command to create the sales table:
Querying the ALL_TAB_PARTITIONS view displays the partition names:
To delete the americas partition from the sales table, invoke the following command:
Querying the ALL_TAB_PARTITIONS view demonstrates that the partition has been successfully deleted:

3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.10 ALTER TABLE… DROP PARTITION

Table of Contents Previous Next