dropping partitioned tables with global indexes?

Question

PROCEDURE purge_partitions
   (
      p_owner            IN VARCHAR2
     ,p_name             IN VARCHAR2
     ,p_retention_period IN NUMBER
   ) IS
   BEGIN
      FOR partition_rec IN (SELECT partition_name
                                  ,high_value
                              FROM dba_tab_partitions
                             WHERE table_owner = p_owner
                               AND table_name = p_name)

      LOOP
         IF SYSDATE >= add_months(to_date(substr(partition_rec.high_value
                                                ,12
                                                ,19)
                                         ,'YYYY-MM-DD HH24:MI:SS')
                                 ,p_retention_period)
         THEN
            execute_immediate('ALTER TABLE ' || p_owner || '.' ||
                              p_name || ' DROP PARTITION ' ||
                              partition_rec.partition_name)
         END IF;
      END LOOP;
   END purge_partitions;

Purge_Partitions procedure deals with dropping partitions based on specific retention priods mentioned in a seperate Config Table. I am now trying to enhance this functionality which will take care of rebuilding global indexes of those partitioned tables. Not sure how to go about this, any help is highly appreciated.

Just append `UPDATE GLOBAL INDEXES`, that's it. Since `HIGH_VALUE` is of data type `LONG` you should better use procedure like this: https://stackoverflow.com/a/48089939/3027266 Otherwise you may drop the wrong partition. — Wernfried Domscheit, Jan 11 '19 at 14:44
Alternatively use [partition_extended_name](https://stackoverflow.com/a/52171170/4808122), so you do not need the partition name at all. — Marmite Bomber, Jan 11 '19 at 16:25

Marmite Bomber · Accepted Answer · 2019-01-11T14:49:17.973

Consider the update_index_clauses, which keeps the indexes valid.

See the documentation and consideration for global indexes here

in your case it will be:

 alter table ttttt drop partition pppppp update global indexes;

Alternatively let the indices be invalidated in the DROP PARTITION and rebuild them with alter index xxx rebuild. You can get the list of indexes to rebuild from this query

select OWNER, INDEX_NAME  
from all_indexes 
where owner = 'ooo' and table_name = 'tttttt' and status = 'UNUSABLE';

dropping partitioned tables with global indexes?

1 Answers1

Linked