Working on a routine for dropping old partitions from a log table, I realized that I don’t have to know the name of the partition I want to drop. I can just specify a value for the partition key and Oracle resolves the partition for me. Here’s how.

The trick is to add the keyword “FOR”. This can be used in statements like, for example, select or partition drop:

ALTER TABLE MyTable DROP PARTITION FOR (key_value);

If you have a subpartitioned table, you can specify the subpartition key as well:

ALTER TABLE MyTable DROP SUBPARTITION FOR (key_value, subkey_value);
SELECT COUNT(*) FROM MyTable SUBPARTITION FOR (TO_TIMESTAMP('20092018', 'DDMMYYYY'), 4);

Neat trick, but you need to have control over the definition of your partitions, so you don’t drop a month of data when you thought you were dropping for a day or a week. For my case, with a log table partitioned on date and subpartitioned on log level, this was a perfect match for dropping old log partitions in a flash.

Note that specifying a value for a non existing partition does not raise an error.

A quick example follows:

SQL> CREATE TABLE MYTABLE (ID NUMBER)
  2  PARTITION BY RANGE (ID) INTERVAL (2)
  3  (PARTITION FIRST VALUES LESS THAN (3));
Table created

SQL> INSERT INTO MYTABLE
  2  SELECT LEVEL
  3  FROM DUAL
  4  CONNECT BY LEVEL <= 5;
5 rows inserted

SQL> SELECT * FROM MYTABLE;
        ID
----------
         1
         2
         3
         4
         5

SQL> ALTER TABLE MYTABLE DROP PARTITION FOR (3);
Table altered
-- Partition for key=3 contains values 3 and 4

SQL> SELECT * FROM MYTABLE;
        ID
----------
         1
         2
         5

SQL> SELECT COUNT(*)
  2  FROM MYTABLE PARTITION FOR (6);
  COUNT(*)
----------
         1

SQL> SELECT COUNT(*)
  2  FROM MYTABLE PARTITION FOR (10);
  COUNT(*)
----------
         0

SQL> DROP TABLE MYTABLE PURGE;
Table dropped