I have task to drop empty (0 rows) partitions and subpartitions from data warehouse.
I'm preparing script that:
Will count the rows of partition/subpartition
When row count will be 0 the partition should be dropped (and confirmation message)
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_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?