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

3. 启用停用TRIGGER

cat enable_trigger.sh

#!/bin/sh

ORACLE_HOME=/u01/product/oracle/11.2.0/db_1;export ORACLE_HOME
ORACLE_SID=szxonline;export ORACLE_SID

/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
alter trigger system.trg_audit_db_ddl enable;
exit;
EOF

cat disable_trigger.sh

#!/bin/sh

ORACLE_HOME=/u01/product/oracle/11.2.0/db_1;export ORACLE_HOME
ORACLE_SID=szxonline;export ORACLE_SID

/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
alter trigger system.trg_audit_db_ddl disable;
exit;
EOF

 

4. 存储过程（取消循环处理）

CREATE OR REPLACE PROCEDURE TOPUP.sp_tab_partitions_maintain (
   USER_NAME   IN VARCHAR2,
   TAB_NAME    IN VARCHAR2
)
IS
   G_USER_NAME   VARCHAR2 (50);
   G_TAB_NAME    VARCHAR2 (50);

   PROCEDURE SP_TAB_ADD_PARTITIONS (LUSER_NAME   IN VARCHAR2,
                                    LTAB_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 := LUSER_NAME;
      V_TAB_NAME := LTAB_NAME;
      v_partition := '0';
      v_create := '0';
      v_type := 'add_partition';
      V_OWNER := UPPER (V_USER_NAME);

 

      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;
   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 (LUSER_NAME   IN VARCHAR2,
                                    LTAB_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 := LUSER_NAME;
      V_TAB_NAME := LTAB_NAME;
      v_partition := '0';
      v_create := '0';
      v_type := 'del_partition';
      v_flag := '0';
      V_OWNER := UPPER (V_USER_NAME);

 

      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, '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;
      END IF;
   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 := USER_NAME;

   G_TAB_NAME := TAB_NAME;

   SP_TAB_ADD_PARTITIONS (G_USER_NAME, G_TAB_NAME);

   SP_TAB_DEL_PARTITIONS (G_USER_NAME, G_TAB_NAME);
END sp_tab_partitions_maintain;
/