oracle定时删除分区

 --*********************************************************
--需求简述： pscp_sms_sent_status分区表
--功能说明: 删除分区
--*******************************************************
   PROCEDURE drop_status_partition

   IS
      ---分区表信息
      CURSOR cur_utp
      IS
         SELECT   utp.table_name, utp.tablespace_name, utp.partition_name,
                  utp.high_value, utp.high_value_length,
                  utp.partition_position
             FROM user_tab_partitions utp
            WHERE utp.table_name = UPPER ('PSCP_SMS_SENT_STATUS')
              and utp.partition_name != 'PSCP_SMS_SENT_STATUS_1'
         ORDER BY utp.partition_position;

      v_high_value           VARCHAR2 (255);            --less than value信息
      v_partition_max_date   DATE;
                                  ----less than value信息的 timestamp表示形式
      v_sqlexec              VARCHAR2 (2000);                   --DDL语句变量
      v_count                NUMBER          := 0;

      v_end_date             DATE            := TRUNC (SYSDATE) - 5;
      v_is_over_time boolean;
      i number := 0;
      v_over_time date;
      v_is_null NUMBER          := 0;  --分区表是否为空标志位
   BEGIN
      v_over_time := TO_DATE ((TO_CHAR ((SYSDATE), 'yyyy-mm-dd') || '12:00:00'),
                  'yyyy-mm-dd hh24:mi:ss');
      v_is_over_time := false;
      FOR utp IN cur_utp
      LOOP
         select sysdate-v_over_time into i from dual;
         if (i > 0) then
            v_is_over_time := true;
         else
         v_high_value := SUBSTR (utp.high_value, 11, 19);

         v_partition_max_date :=
                              TO_DATE (v_high_value, 'yyyy-mm-dd hh24:mi:ss');


         IF v_partition_max_date IS NOT NULL AND v_partition_max_date < v_end_date
         THEN
            v_sqlexec :=
                  'ALTER TABLE '
               || utp.table_name
               || ' DROP PARTITION '
               || utp.partition_name
               || ' update global indexes';
             dbms_output.put_line('删除' || utp.table_name || '表分区=' || v_SqlExec);
            DBMS_UTILITY.exec_ddl_statement (v_sqlexec);
            v_count := v_count + 1;
         END IF;

         ------判断是否不需要再删除 ,跳出循环
         EXIT WHEN v_partition_max_date > v_end_date;
         end if;
         EXIT WHEN v_is_over_time;
      END LOOP;

   END drop_status_partition;

END pkg_pscp_partition_alter;