自动删除按时间自增量分区job

分区为按天分区，删除一个月前的分区

先创建自定义函数long_2_varchar

-------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE

 Sql_statemet VARCHAR2(1000);
CURSOR  p  IS
  select table_name,partition_name
  from (select table_name,
               partition_name,
               long_2_varchar(table_name, partition_name) t
          from user_tab_partitions
         where table_name in ('T_CIMS_ODS_RECEIVEEXPERSS'))
         where to_date(substr(t,11,19),'yyyy-mm-dd hh24:mi:ss')<sysdate-32
         and partition_name like 'SYS_%';  
    
begin
   for x in p loop
 
   
    Sql_statemet:='alter table '||x.TABLE_NAME||' drop partition '||x.PARTITION_NAME||'  update global indexes';
    DBMS_OUTPUT.PUT_LINE(Sql_statemet);
    execute immediate Sql_statemet;
end loop;  

end;

-------------------------------------------------------------------------------------------------------------------------------------------------------