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

来源：互联网发布：优化科技创新生态系统编辑：程序博客网时间：2024/06/01 09:58

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

先创建自定义函数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;

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

0 0