删除具有全局索引的分区表? [英] dropping partitioned tables with global indexes?

问题描述

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过程处理基于以下操作删除分区 在单独的配置表中提到的特定保留原则.我是 现在尝试增强此功能，这将解决 重建那些分区表的全局索引.不确定如何执行此操作，非常感谢您的帮助.

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.

推荐答案

请考虑 update_index_clauses ，可保持索引有效.

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;

或者让索引在DROP PARTITION中无效，然后用alter index xxx rebuild重建它们.您可以从该查询中获取要重建的索引列表

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';

这篇关于删除具有全局索引的分区表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！