Dropping empty partitions in Oracle 11g DB

Question

I have task to drop empty (0 rows) partitions and subpartitions from data warehouse.

I'm preparing script that:

1. Will count the rows of partition/subpartition

2. When row count will be 0 the partition should be dropped (and confirmation message)

3. When row count is different than 0, message that this partition was not empty

To check number of rows I created SELECT 'partition_name', COUNT(*) FROM user.table PARTITION (partition_name); I was advised that count will be better way than basing on NUM_ROWS column of DBA_TAB_PARTITIONS or DBA_TAB_SUBPARTITIONS (however from those 2 I will extract the list of potential candidates with other data to excel for later analysis). I was also advised not to use dbms.output.

There are millions of potential candidates (10M+ on some dbs).On analysis stage I plan to exclude 'MAXVALUE' partitions and HASH type using Excel filters and then use above script.

Does someone have similar script? I need pattern for one partition and then I will generate rest in Excel. Are there more things to exclude from drop list on analysis stage?

Answer 1

I am using a procedure like this:

PROCEDURE MaintainPartitions IS 

    CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME LIKE ....
    ORDER BY TABLE_NAME, PARTITION_POSITION;

    cur SYS_REFCURSOR;
    r INTEGER;
    highValue VARCHAR2(100);

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue NOT IN ('DEFAULT', 'MAXVALUE') THEN
         OPEN cur FOR 'SELECT ROWNUM FROM '||aTab.TABLE_NAME||' PARTITION ('||aTab.PARTITION_NAME||') WHERE ROWNUM <= 1';
         FETCH cur INTO r;
         IF cur%NOTFOUND THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' DROP PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
         END IF;
         CLOSE cur;    
      END IF;
   END LOOP;
END;

Note, you cannot drop a subpartition. In such case you have to use TRUNC.

Another note, in case of INTERVAL partitions you may face ORA-14758: Last partition … cannot be dropped. Have a look at https://stackoverflow.com/questions/1395379/unable-to-delete-oldest-table-partition to manage it.