ORACLE 分区表定期自动分区导出及删除导出分区(三)

3. 存储过程每天自动增加第二天的分区，保证第二天生产需要，删除分区的部分则根据前面记录的表的状态，利用存储过程判断删除分区是否已经成功导出，
若记录时间、表名、分区名对应状态为1,则删除该分区，否则不作处理，存储过程代码如下:

CREATE OR REPLACE PROCEDURE TOPUP.sp_tab_maintain_partitions
IS
   G_USER_NAME   VARCHAR2 (50);

   PROCEDURE SP_TAB_ADD_PARTITIONS (USER_NAME IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      V_USER_NAME      VARCHAR2 (50);
      V_TAB_NAME       VARCHAR2 (100);
      V_OWNER          VARCHAR2 (50);
      V_DAY            DATE;
      V_NEXT_DATE      DATE;
      V_DATE_FORMAT1   VARCHAR2 (4000);
      V_DATE_FORMAT2   VARCHAR2 (4000);
      V_ADD_SQL_1      VARCHAR2 (4000);
      V_ADD_SQL_2      VARCHAR2 (4000);
      V_ADD_SQL        VARCHAR2 (4000);
   BEGIN
      V_USER_NAME := USER_NAME;

      V_TAB_NAME := '0';

      V_OWNER := UPPER (V_USER_NAME);

      DECLARE
         CURSOR PART_TABLE_NAME
         IS
            SELECT   TABLE_NAME
              FROM   ALL_PART_TABLES
             WHERE   WNER = V_OWNER;
      BEGIN
         OPEN PART_TABLE_NAME;

         LOOP
            FETCH PART_TABLE_NAME INTO   V_TAB_NAME;

            EXIT WHEN PART_TABLE_NAME%NOTFOUND;

 

            V_DATE_FORMAT1 :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyymmdd'
               || '''';

            EXECUTE IMMEDIATE V_DATE_FORMAT1;

            SELECT   SYSDATE + 1 INTO V_DAY FROM DUAL;

            V_ADD_SQL_1 :=
                  'ALTER TABLE '
               || V_USER_NAME
               || '.'
               || V_TAB_NAME
               || '  ADD PARTITION P'
               || V_DAY
               || ' VALUES LESS THAN (TIMESTAMP'
               || '''';

            V_DATE_FORMAT2 :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyy-mm-dd'
               || '''';

            EXECUTE IMMEDIATE V_DATE_FORMAT2;

            SELECT   SYSDATE + 2 INTO V_NEXT_DATE FROM DUAL;

            V_ADD_SQL_2 :=
                  V_NEXT_DATE
               || ' 00'
               || ':'
               || '00'
               || ':'
               || '00'
               || ''
               || ''''
               || ')';

 

            V_ADD_SQL := V_ADD_SQL_1 || V_ADD_SQL_2;

            EXECUTE IMMEDIATE V_ADD_SQL;
         END LOOP;

         CLOSE PART_TABLE_NAME;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE(V_OWNER || ':' || V_TAB_NAME
                              || ' ADD PARTITION ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!');
         ROLLBACK;
   END;

   PROCEDURE SP_TAB_DEL_PARTITIONS (USER_NAME IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;

      V_USER_NAME     VARCHAR2 (50);
      V_TAB_NAME      VARCHAR2 (100);
      v_partition     VARCHAR2 (50);
      V_OWNER         VARCHAR2 (50);
      V_7D_AGO_DATE   DATE;
      v_create        VARCHAR2 (50);
      v_flag          VARCHAR2 (1);
      V_DATE_FORMAT   VARCHAR2 (4000);
      V_DEL_SQL       VARCHAR2 (4000);
   BEGIN
      V_USER_NAME := USER_NAME;
      V_TAB_NAME := '0';
      v_partition := '0';
      v_create := '0';
      v_flag := '0';
      V_OWNER := UPPER (V_USER_NAME);

      DECLARE
         CURSOR PART_TABLE_NAME
         IS
            SELECT   TABLE_NAME
              FROM   ALL_PART_TABLES
             WHERE   WNER = V_OWNER;
      BEGIN
         OPEN PART_TABLE_NAME;

         LOOP
            FETCH PART_TABLE_NAME INTO   V_TAB_NAME;

            EXIT WHEN PART_TABLE_NAME%NOTFOUND;

            V_DATE_FORMAT :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyymmdd'
               || '''';

            EXECUTE IMMEDIATE V_DATE_FORMAT;

            SELECT   SYSDATE - 7 INTO V_7D_AGO_DATE FROM DUAL;

            V_DEL_SQL :=
                  'ALTER TABLE '
               || V_USER_NAME
               || '.'
               || V_TAB_NAME
               || ' DROP PARTITION P'
               || V_7D_AGO_DATE
               || ' update global indexes';

            SELECT   TO_CHAR (SYSDATE - 7, 'yyyymmdd') INTO v_create FROM DUAL;

            SELECT   'P' || V_7D_AGO_DATE INTO v_partition FROM DUAL;

            SELECT   flag
              INTO   v_flag
              FROM   tab_partition_mantain_record
             WHERE       createdate = v_create
                     AND TABLENAME = V_TAB_NAME
                     AND PARTITIONNAME = v_partition;

            IF v_flag = '1'
            THEN
               EXECUTE IMMEDIATE V_DEL_SQL;
            ELSE
               EXIT;
            END IF;
         END LOOP;

         CLOSE PART_TABLE_NAME;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE(V_OWNER || ':' || V_TAB_NAME
                              || ' DEL PARTITION ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!');
         ROLLBACK;
   END;
BEGIN
   G_USER_NAME := 'TOPUP';

   SP_TAB_ADD_PARTITIONS (G_USER_NAME);

   SP_TAB_DEL_PARTITIONS (G_USER_NAME);
END SP_TAB_MAINTAIN_PARTITIONS;
/

4.经测试基本满足要求，若有更多需求，请变更相关脚本

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-735910/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-735910/