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 series of images illustrates the difference between truncating a partition and dropping a partition.

Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.You cannot truncate an index partition. However, if local indexes are defined for the table, the

ALTER TABLE ... TRUNCATE PARTITION

statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt.

You can drop empty segments in tables and table fragments with the DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS procedure. In addition, if a partition or subpartition has a segment, then the truncate feature drops the segment if the DROP ALL STORAGE clause is specified with the ALTER TABLE TRUNCATE PARTITION SQL statement.
The next lesson demonstrates how to split and merge partitions.