ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 改进版ORACLE 分区表定期自动分区导出及删除导出分区(三)

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

原创 Linux操作系统 作者:golden_zhou 时间:2012-07-19 15:26:38 0 删除 编辑
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_partition      VARCHAR2 (50);
      v_create         VARCHAR2 (50);
      v_type           VARCHAR2 (50);
      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_partition := '0';
      v_create := '0';
      v_type := 'add_partition';
      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   TO_CHAR (SYSDATE, 'yyyymmdd') INTO v_create FROM DUAL;
            SELECT   SYSDATE + 1 INTO V_DAY FROM DUAL;
            SELECT   'P' || V_DAY INTO v_partition 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;
            INSERT INTO tab_partition_mantain_record
              VALUES   (v_create,
                        v_type,
                        V_TAB_NAME,
                        v_partition,
                        1);
            COMMIT;
         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_type          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_type := 'del_partition';
      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 - 3 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, '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;
               INSERT INTO tab_partition_mantain_record
                 VALUES   (v_create,
                           v_type,
                           V_TAB_NAME,
                           v_partition,
                           1);
               COMMIT;
            ELSE
               INSERT INTO tab_partition_mantain_record
                 VALUES   (v_create,
                           v_type,
                           V_TAB_NAME,
                           v_partition,
                           0);
               COMMIT;
               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-736030/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    291742