Drop历史INTERVAL分区PLSQL范例

--本范例是先drop相关分区，再一次性并行重建不可用的索引

set serveroutput on;
DECLARE
  dt           DATE;
  l_keep_days  NUMBER := 6; --保留时间
  l_table_name VARCHAR2(200) := 'QM20220114'; --要清理的表名
BEGIN

--1. 先drop 分区,不重建索引
 dbms_output.put_line('1.先drop分区,不重建索引');
  FOR x IN (SELECT ut.table_name,
                   ut.partition_name,
                   ut.high_value
              FROM user_tab_partitions ut
             WHERE ut.table_name = l_table_name
              AND INTERVAL = 'YES'
             ORDER BY ut.partition_position)
  LOOP
    EXECUTE IMMEDIATE 'select ' || x.high_value || ' FROM dual'
      INTO dt;
    IF dt < SYSDATE - l_keep_days
    THEN
      dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)|| x.table_name || '.' ||
                           x.partition_name || ' value less then ' ||
                           to_char(dt,'yyyy-mm-dd')||': alter TABLE ' || x.table_name ||' drop PARTITION ' || x.partition_name||';');    
      --下面的EXECUTE IMMEDIATE会drop分区，执行时请慎重确认代码无误   
     EXECUTE IMMEDIATE 'alter TABLE ' || x.table_name ||' drop PARTITION ' || x.partition_name; 
     dbms_output.put_line('--- drop partition Finished ! ' ); 
    END IF;
  END LOOP;
  
--2.重建因drop分区而不可用的全局索引    
  dbms_output.put_line(CHR(10)||'2.重建因drop分区而不可用的全局索引' );  
  FOR y IN (SELECT owner,
       index_name,
       table_name,
       table_owner,
       'alter index ' || owner || '.' || index_name ||
       ' rebuild online parallel 4 tablespace ' || tablespace_name  as rebuild_index_pl ,
       'alter index ' || owner || '.' || index_name ||' noparallel'  as alert_index_no_pl
  FROM dba_indexes where table_name=l_table_name AND partitioned='NO' and  status = 'UNUSABLE' order by index_name
)
 LOOP
  dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.rebuild_index_pl||';');
    dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.alert_index_no_pl||';');
    EXECUTE IMMEDIATE y.rebuild_index_pl;
  EXECUTE IMMEDIATE y.alert_index_no_pl;
  dbms_output.put_line('--- Rebuild Finished !');  

    END LOOP;

--3.重建因drop分区而不可用的分区索引 
  dbms_output.put_line(CHR(10)||'3.重建因drop分区而不可用的分区索引' );     
FOR g IN (SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild partition ' || s.partition_name || ' online parallel 4 TABLESPACE ' ||s.tablespace_name  as rebuild_p_index_pl,
     'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_partitions s,dba_indexes d where s.index_name=d.index_name and d.table_name = l_table_name and s.status = 'UNUSABLE' order by index_name
)
 LOOP
  dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.rebuild_p_index_pl||';');
    dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.alert_p_index_no_pl||';');
   EXECUTE IMMEDIATE g.rebuild_p_index_pl;
     EXECUTE IMMEDIATE g.alert_p_index_no_pl;
  dbms_output.put_line('--- Rebuild Finished !');  

    END LOOP;  
    
--4.重建因drop分区而不可用的子分区索引   
   dbms_output.put_line(CHR(10)||'4.重建因drop分区而不可用的子分区索引 ' );   
FOR y IN (
SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild subpartition ' || s.subpartition_name || ' TABLESPACE ' ||s.tablespace_name as rebuild_index
  FROM dba_ind_subpartitions s,dba_indexes d
 WHERE  s.index_name =d.index_name and d.table_name = l_table_name and s.status = 'UNUSABLE' order by index_name
  
)
 LOOP
  dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.rebuild_index);
    EXECUTE IMMEDIATE y.rebuild_index;
  dbms_output.put_line('--- Rebuild Finished !');  

    END LOOP;    
           
END;