0

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?

  • 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. – Raj Jun 26 '17 at 16:37
  • 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 Jun 26 '17 at 17:30
  • 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. – XYZ-12c Jun 26 '17 at 17:38
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.