Dropping Table Partition in Oracle 19c

In Oracle 19c, a DBA can remove a table partition along with its data using the `ALTER TABLE ... DROP PARTITION` command.
This operation removes only the specified partition, not the entire table, and deletes all data within that partition. Here's a detailed guide:
Steps to Drop a Partition in Oracle 19c

1. Verify the Partitioned Table Before dropping a partition, confirm the table is partitioned and identify the partition to be dropped:

         SELECT table_name, partition_name, high_value
         FROM user_tab_partitions
         WHERE table_name = '<TABLE_NAME>';
       

   This query lists the partitions for the specified table.
2. Drop the Partition Use the ALTER TABLE statement to drop the partition. Example:

         ALTER TABLE <TABLE_NAME> DROP PARTITION <PARTITION_NAME>;
       

   Explanation:
   • <TABLE_NAME>: The name of the partitioned table.
   • <PARTITION_NAME>: The name of the partition to be dropped.
   Example:

         ALTER TABLE sales DROP PARTITION sales_2023_q1;
       

3. Behavior of DROP PARTITION:
   • The partition's data is removed permanently.
   • The partition definition is also removed from the table's metadata.
   • Other partitions remain unaffected.

Additional Options and Considerations

1. Cascading Referential Constraints If the table has child tables with referential integrity constraints, include the CASCADE CONSTRAINTS clause:

         ALTER TABLE <TABLE_NAME> DROP PARTITION <PARTITION_NAME> CASCADE CONSTRAINTS;
       

2. Purging the Data By default, Oracle moves the partition's data to the recycle bin. To bypass the recycle bin and permanently delete the data, use the PURGE clause:

         ALTER TABLE <TABLE_NAME> DROP PARTITION <PARTITION_NAME> PURGE;
       

3. Dropping a Subpartition If the table uses subpartitioning, you can drop a specific subpartition:

         ALTER TABLE <TABLE_NAME> DROP SUBPARTITION <SUBPARTITION_NAME>;
       

4. Maintaining Global Indexes Dropping a partition can invalidate global indexes. Use the UPDATE GLOBAL INDEXES clause to keep the indexes valid:

         ALTER TABLE <TABLE_NAME> DROP PARTITION <PARTITION_NAME> UPDATE GLOBAL INDEXES;
       

5. Querying Partition Information After dropping the partition, verify the table's remaining partitions:

         SELECT table_name, partition_name
         FROM user_tab_partitions
         WHERE table_name = '<TABLE_NAME>';
       

Example Workflow
Given a partitioned table `sales` with partitions by quarter:

1. Verify partitions:

         SELECT partition_name, high_value
         FROM user_tab_partitions
         WHERE table_name = 'SALES';
       

2. Drop the sales_2023_q1 partition:

         ALTER TABLE sales DROP PARTITION sales_2023_q1 PURGE;
       

3. Verify remaining partitions:

         SELECT partition_name
         FROM user_tab_partitions
         WHERE table_name = 'SALES';
       

Important Considerations

• Backup First: Ensure you have a backup of the table or partition data before performing the operation.
• Impact on Performance: Dropping large partitions may cause high I/O and lock the table temporarily.
• Recycle Bin: Without the PURGE option, data can be restored from the recycle bin if needed.

This approach allows an Oracle DBA to manage partitioned tables effectively while ensuring data integrity and optimal performance.

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.
DROP PARTITION
You can drop a partition and its data with the

ALTER TABLE DROP PARTITION partition_name.

When you drop a partition, the corresponding local index partitions^[1] 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.

• TRUNCATE PARTITION 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.

[1] local index partitions: Local index partitions in Oracle partitioned tables are index partitions that are tied to a specific table partition. This means each index partition only contains entries for rows in its corresponding table partition, simplifying maintenance and potentially improving performance for queries that target specific partitions.