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;
/
