Dropping or splitting partitions

Oracle Tips by Burleson

Modifying Table Partitions

You can modify a table or table partition in any of the ways described in the subsections following. You cannot combine partition operations with other partition operations or with operations on the base table in one ALTER TABLE statement. You cannot move a partition with subpartitions; each subpartition must be individually moved.

Add Partition. Use ALTER TABLE ADD PARTITION to add a partition to the high end of the table (after the last existing partition). If the first element of the partition bound of the high partition is MAXVALUE, you cannot add a partition to the table. You must split the high partition.

You can add a partition to a table even if one or more of the table indexes or index partitions are marked UNUSABLE.

You must use the SPLIT PARTITION clause to add a partition at the beginning or the middle of the table.

The following example adds partition jan99 to tablespace yr99 for table sales:

See Code Depot

Drop Partition. ALTER TABLE DROP PARTITION drops a partition and its data. If you want to drop a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions.

If you drop a partition and later insert a row that would have belonged to the dropped partition, the row will be stored in the next higher partition. However, if you drop the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.