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?

• Raj: num_rows is reliable when you let optimizer collect statistics on the object. you could let Oracle collect stats (even at 1% you will know which ones are empty). Then you can write a loop and either drop or merge those with the higher/lower partition based on your requirements. shouldn't include MAXVALUE partition unless you have code that manages partition creation correctly. You already have SQL, do you still need excel to analyze which partitions to drop? not that there is anything odd about it, but i am not sure why, but that is just me.
• XYZ-12c: I like one centralized decision making place. I can put recommendations in Excel, modify it for special orders(if for instance some partitions shouldn't be touched), generate scripts using functions, add comments etc.
• XYZ-12c: I think I can't trust statistics, column 'last_analyzed' has 2-3 year old values. I need to be 100% sure that there is no data inside.

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.