create or replace
PROCEDURE             Station_DROP_PARTITION_PROC(
 beforeDays NUMBER  --删除多少天前的分区
)
As
 v_SqlExec  VARCHAR2(2000); --DDL语句变量
 v_err_num  NUMBER;  --ORA错误号
 v_err_msg  VARCHAR2(100); --错误描述

 --查找beforeDays天前存在的 mals_nm_cpu_info_t 表分区
 cursor cursor_cpu_info_part is
 select partition_name from user_tab_partitions
 WHERE table_name=UPPER('StationRealTime')
 AND SUBSTR(partition_name,9,8) ORDER BY partition_name;
 
 ---为禁用或启用约束而定义的游标
    cursor cursor_const_part is
    select b.constraint_name
      from user_constraints a, user_cons_columns b
      where a.table_name=b.table_name
        and   a.constraint_name=b.constraint_name
        and   a.owner='TICKETS'
        and   a.table_name=upper('StationRealTime');
       
    ---为索引失效而重建索引定义游标
    cursor cursor_idx is
    select index_name
      from user_indexes
     where table_name = UPPER('StationRealTime')
       and status = 'UNUSABLE';
      
record_cpu_info_oldpart cursor_cpu_info_part%rowType;
record_cursor_const_part cursor_const_part%rowtype;
record_cursor_idx cursor_idx%rowtype;
 

BEGIN

---暂时禁用约束
    open cursor_const_part;
    loop
      fetch cursor_const_part into record_cursor_const_part;
      exit when cursor_const_part%notfound;
   
      execute immediate 'alter table StationRealTime disable constraint '||record_cursor_const_part.constraint_name||' cascade';
   
    end loop;
    close cursor_const_part;

 open cursor_cpu_info_part;
  loop
   fetch cursor_cpu_info_part into record_cpu_info_oldpart;
   exit when cursor_cpu_info_part%notfound;
   --删除 mals_nm_cpu_info_t 表分区
   v_SqlExec:='ALTER TABLE StationRealTime DROP PARTITION ' ||
   record_cpu_info_oldpart.partition_name;
   dbms_output.put_line('删除AlexMonitor.StationRealTime表分区='||v_SqlExec);
   DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
  end loop;
 close cursor_cpu_info_part;
 
 ---启用约束
       open cursor_const_part;
         loop
          fetch cursor_const_part into record_cursor_const_part;
          exit when cursor_const_part%notfound;
         
           execute immediate 'alter table StationRealTime enable novalidate constraint '||record_cursor_const_part.constraint_name;
         
         end loop;
         close cursor_const_part;
        
     --重建失效的索引
        open cursor_idx;
        loop
          fetch cursor_idx into record_cursor_idx;
          exit when cursor_idx%notfound;
         
          execute immediate 'alter index ' ||record_cursor_idx.index_name||' rebuild';
        
         end loop;
         close cursor_idx;

EXCEPTION
 WHEN OTHERS THEN
  v_err_num := SQLCODE;
  v_err_msg := SUBSTR(SQLERRM, 1, 100);
  dbms_output.put_line('AlexMonitor.Station_DROP_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);
END Station_DROP_PARTITION_PROC;

--执行删除的存储过程: