Dropping Oracle Partitions

Sometimes you must delete objects from your database. You can remove a table and its data by simply dropping the table.
Since a partition is an object contained within the larger object of a table, you can also get rid of either the entire partition or just the partition's data.

You can drop a partition and its data with the

command ALTER TABLE DROP PARTITION partition_name.

When you drop a partition, the corresponding local index partitions also are dropped. Any global non-partitioned indexes or any portions of global partitioned indexes with reference to the rows that existed in the partition are marked as unusable.
If you drop a partition in the middle of a table, any future INSERTs into the partition will go into the next higher partition. If you drop the highest partition, you have effectively lowered the upper bound for the table, so any INSERTs that would have gone into this partition will fail.

You can get rid of all the data in a partition, but keep the partition in place with the ALTER TABLE TRUNCATE PARTITION partition_name command. This command can have either the DROP STORAGE clause, which deallocates the storage used by the partition, or REUSE STORAGE, which maintains the previously allocated storage space. Truncating a partition also has the effect of truncating the corresponding local index partition and marking the partition as usable, even if it were unusable before. If the truncated partition was not empty, the operation marks global non-partitioned indexes and any portions of global partitioned indexes with references to the truncated rows as unusable.
The following Slide Show illustrates the difference between truncating a partition and dropping a partition.

Dropping Truncate Partitions
The next lesson demonstrates how to split and merge partitions.