2014年9月16日 星期二

定期刪除table過期資料

1. 建立執行清除資料的 Table 候選清單
    create table "zdba_del_tab"
2. 建立 Procedure 只處理 delete 動作
    create procedure "zdba_delete_commit"
3. 建立 Procedure 呼叫 "zdba_delete_commit" 處理 Table清單 "zdba_del_tab" 中的 Table
    create procedure "zdba_del_tab_p""zdba_delete_commit" 做刪除動作
4. 建立 Job 定期自動執行


1.
CREATE TABLE CMO.ZDBA_DEL_TAB
(
  OWNER         VARCHAR2(30 BYTE),
  SEGMENT_NAME  VARCHAR2(81 BYTE),
  KEYCOL        VARCHAR2(30 BYTE),
  CONDITION     VARCHAR2(10 BYTE),
  KEYCOLVAL     VARCHAR2(100 BYTE),
  ENABLED       VARCHAR2(1 BYTE),
  STIME         DATE,
  ETIME         DATE,
  CONDITION2    VARCHAR2(500 BYTE),
  SHRINK        VARCHAR2(1 BYTE),
  STATUS        VARCHAR2(20 BYTE)
)
TABLESPACE <tablespace_name>;

COMMENT ON TABLE ZDBA_DEL_TAB IS
'Purpose: Table List for Delete Expired Table Data  
Created by: DBA  
Keep days: Always  
Purge key: NA  
Desc:  
  enabled=A :簡單型, 每日1:00執 delete  by crontab
  enabled=B :簡單型, 每日5:00執 delete  by DB job
  enabled=C :複雜型, 每日1:00執 delete  by crontab in
  enabled=X :不執行 delete';

COMMENT ON COLUMN ZDBA_DEL_TAB.SHRINK IS '是否做shrink動作';


CREATE UNIQUE INDEX ZDBA_DEL_TAB_U01 ON ZDBA_DEL_TAB
(OWNER, SEGMENT_NAME)
LOGGING
TABLESPACE <tablespace_name>;


ALTER TABLE ZDBA_DEL_TAB ADD (
  CONSTRAINT ZDBA_DEL_TAB_U01
 UNIQUE (OWNER, SEGMENT_NAME)
    USING INDEX
    TABLESPACE <tablespace_name>);


2.
CREATE OR REPLACE PROCEDURE zdba_delete_commit (
   p_statement           IN   VARCHAR2,
   p_commit_batch_size   IN   NUMBER DEFAULT 10000
)
IS
/* ----------------------------------
Purpose: Delete Table in Batch Mode
Created by: DBA
Date:
structure:
  1. define sql statement
  2. open cursor
  3. execute cursor
  4. close cursor
updated:
---------------------------------- */
   cid                 INTEGER;
   changed_statement   VARCHAR2 (2000);
   finished            BOOLEAN;
   nofrows             INTEGER;
   lrowid              ROWID;
   rowcnt              INTEGER;
   errpsn              INTEGER;
   sqlfcd              INTEGER;
   errc                INTEGER;
   errm                VARCHAR2 (2000);
BEGIN
-- If the actual statement contains a WHERE clause, then append a
-- rownum < n clause after that using AND, else use WHERE rownum < n clause
   IF (UPPER (p_statement) LIKE '% WHERE %')
   THEN
      changed_statement :=
         p_statement || ' AND rownum < ' || TO_CHAR (p_commit_batch_size + 1);
   ELSE
      changed_statement :=
         p_statement || ' WHERE rownum < '
         || TO_CHAR (p_commit_batch_size + 1);
   END IF;

   BEGIN
      cid := DBMS_SQL.open_cursor;              -- Open a cursor for the task
      DBMS_SQL.parse (cid, changed_statement, DBMS_SQL.native);
                                                         -- parse the cursor.
      rowcnt := DBMS_SQL.last_row_count;
   -- store for some future reporting
   EXCEPTION
      WHEN OTHERS
      THEN
         errpsn := DBMS_SQL.last_error_position;
                               -- gives the error position in the changed sql
                               -- delete statement if anything happens
         sqlfcd := DBMS_SQL.last_sql_function_code;
                                     -- function code can be found in the OCI
                                     -- manual
         lrowid := DBMS_SQL.last_row_id;
         -- store all these values for error reporting. However
         -- all these are really useful in a stand-alone proc
         -- execution for DBMS_OUTPUT to be successful, not
         -- possible when called from a form or front-end tool.
         errc := SQLCODE;
         errm := SQLERRM;
         DBMS_OUTPUT.put_line (   'Error:'
                               || TO_CHAR (errc)
                               || ' Posn:'
                               || TO_CHAR (errpsn)
                               || 'SQL fCode '
                               || TO_CHAR (sqlfcd)
                               || ' rowid'
                               || ROWIDTOCHAR (lrowid)
                              );
         raise_application_error (-20000, errm);
                             -- it'll ensure the display of at least the error
                             -- message if something happens.
   END;

   finished := FALSE;

   WHILE NOT (finished)
   LOOP      -- keep on executing the cursor till there is no more to process.
      BEGIN
         nofrows := DBMS_SQL.EXECUTE (cid);
         rowcnt := DBMS_SQL.last_row_count;
      EXCEPTION
         WHEN OTHERS
         THEN
            errpsn := DBMS_SQL.last_error_position;
            sqlfcd := DBMS_SQL.last_sql_function_code;
            lrowid := DBMS_SQL.last_row_id;
            errc := SQLCODE;
            errm := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error:'
                                  || TO_CHAR (errc)
                                  || 'Posn:'
                                  || TO_CHAR (errpsn)
                                  || 'SQL fCode '
                                  || TO_CHAR (sqlfcd)
                                  || ' rowid'
                                  || ROWIDTOCHAR (lrowid)
                                 );
            raise_application_error (-20000, errm);
      END;

      IF nofrows = 0
      THEN
         finished := TRUE;
      ELSE
         finished := FALSE;
      END IF;

      COMMIT;
   END LOOP;

   BEGIN
      DBMS_SQL.close_cursor (cid);     -- close the cursor for a clean finish
   EXCEPTION
      WHEN OTHERS
      THEN
         errpsn := DBMS_SQL.last_error_position;
         sqlfcd := DBMS_SQL.last_sql_function_code;
         lrowid := DBMS_SQL.last_row_id;
         errc := SQLCODE;
         errm := SQLERRM;
         DBMS_OUTPUT.put_line (   'Error:'
                               || TO_CHAR (errc)
                               || ' Posn:'
                               || TO_CHAR (errpsn)
                               || 'SQL fCode '
                               || TO_CHAR (sqlfcd)
                               || ' rowid'
                               || ROWIDTOCHAR (lrowid)
                              );
         raise_application_error (-20000, errm);
   END;
END;
/



3.
CREATE OR REPLACE PROCEDURE zdba_del_tab_p (p_enabled IN VARCHAR2)
IS
/*
Purpose: Delete Expired Data
Created by: DBA
Desc: 排crontab 每日 1:00執行
Update:
*/
   CURSOR c1
   IS
      SELECT owner, segment_name, keycol, condition, keycolval, condition2
        FROM zdba_del_tab
       WHERE enabled = p_enabled;

   r1           c1%ROWTYPE;
   str          VARCHAR2 (500);
   v_sqlcode    VARCHAR2 (200);
   v_sqlerrm    VARCHAR2 (200);
   v_stime      DATE;
   v_etime      DATE;
   v_difftime   NUMBER;
   v_sender     VARCHAR2 (200) := 'XX System';
   v_maillist   VARCHAR2 (200)
                         := 'dba@domain,dba_backup@domain';
BEGIN
   OPEN c1;

   IF p_enabled = 'A' or p_enabled = 'B'
   THEN
      SELECT SYSDATE
        INTO v_stime
        FROM DUAL;

      LOOP
         FETCH c1
          INTO r1;

         EXIT WHEN c1%NOTFOUND;

         BEGIN
            UPDATE zdba_del_tab
               SET stime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
            str :=
                  'delete from '
               || r1.owner
               || '.'
               || r1.segment_name
               || ' where '
               || r1.keycol
               || ' '
               || r1.condition
               || ' '
               || r1.keycolval;
            zdba_delete_commit (str);

            UPDATE zdba_del_tab
               SET etime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               raise_application_error (-20001,
                                           'A loop error was encountered '
                                        || SQLCODE
                                        || ' -ERROR- '
                                        || SQLERRM
                                       );
         END;
      END LOOP;

      SELECT SYSDATE
        INTO v_etime
        FROM DUAL;

      v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
   ELSIF p_enabled = 'C'
   THEN
      SELECT SYSDATE
        INTO v_stime
        FROM DUAL;

      LOOP
         FETCH c1
          INTO r1;

         EXIT WHEN c1%NOTFOUND;

         BEGIN
            UPDATE zdba_del_tab
               SET stime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
            str :=
                  'delete from '
               || r1.owner
               || '.'
               || r1.segment_name
               || ' where '
               || r1.condition2;
            zdba_delete_commit (str);

            UPDATE zdba_del_tab
               SET etime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               raise_application_error (-20001,
                                           'A loop error was encountered '
                                        || SQLCODE
                                        || ' -ERROR- '
                                        || SQLERRM
                                       );
         END;
      END LOOP;

      SELECT SYSDATE
        INTO v_etime
        FROM DUAL;

      v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
   ELSE
      NULL;
   END IF;

   CLOSE c1;

   UTL_MAIL.send (sender          => v_sender,
                  recipients      => v_maillist,
                  cc              => NULL,
                  subject         => 'OK...!! Delete Expired Data Completely. Type='||p_enabled||' (apps.zdba_del_tab_p)',
                  MESSAGE         =>    'Total consume '
                                     || v_difftime
                                     || ' min, from '
                                     || TO_CHAR (v_stime,
                                                 'yyyy-mm-dd hh24:mi:ss'
                                                )
                                     || ' to '
                                     || TO_CHAR (v_etime,
                                                 'yyyy-mm-dd hh24:mi:ss'
                                                ),
                  mime_type       => 'text/plain; charset=UTF-8'
                 );
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,
                                  'A main error was encountered '
                               || SQLCODE
                               || ' -ERROR- '
                               || SQLERRM
                              );
      v_sqlcode := SQLCODE;
      v_sqlerrm := SQLERRM;
      UTL_MAIL.send (sender          => v_sender,
                     recipients      => v_maillist,
                     cc              => NULL,
                     subject         => 'ERROR...!! Delete Expired Data Failed. Type='||p_enabled||' (apps.zdba_del_tab_p)',
                     MESSAGE         =>    'SQLCODE: '
                                        || v_sqlcode
                                        || ' ,SQLERRM: '
                                        || v_sqlerrm,
                     mime_type       => 'text/plain; charset=UTF-8'
                    );
END;
/