General
========================
Code example, of keeping only 7 days old data from a huge table, using temp table during the process.
CREATE TABLE MY_TABLE_BAK AS SELECT * FROM MY_TABLE WHERE 1=2;
ALTER TABLE MY_TABLE_BAK NOLOGGING;
INSERT /*+ APPEND */ INTO MY_TABLE_BAK SELECT * FROM MY_TABLE WHERE MY_TABLE.ts_last_modified > SYSDATE - 7;
COMMIT;
TRUNCATE TABLE MY_TABLE;
INSERT /*+ APPEND */ INTO MY_TABLE SELECT * FROM MY_TABLE_BAK;
COMMIT;
DROP TABLE MY_TABLE_BAK;
========================
PL/SQL code example
========================
This is a similar logic, in PL/SQL code, only more generic, with a job.
The code is split to four files:
admin_util_header.sql
admin_util_body.sql
permissions
create job
admin_util_header.sql
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL;
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
PROCEDURE purgeOldData (p_table_name IN VARCHAR2, p_days_to_keep IN NUMBER);
END ADMIN_UTIL;
admin_util_body.sql
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL AS
-- GRANT UNLIMITED TABLESPACE TO
-- GRANT CREATE TABLE TO
--SELECT * FROM SGA_W_LOG_VW WHERE procedure_name = 'ADMIN_UTIL.purgeOldData';
--CREATE OR REPLACE VIEW SGA_W_LOG_VW AS
--SELECT * FROM
--( SELECT PROCEDURE_NAME,data,TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss') ts_last_modified
--FROM SGA_W_LOG WHERE ts_last_modified > SYSDATE-1 ORDER BY ts_last_modified DESC)
--WHERE ROWNUM < 21;
-----------------------------------------------------------
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG (procedure_name, data, ts_last_modified)
VALUES (p_module_name, p_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END WRITE_SGA_W_LOG;
-----------------------------------------------------------
--==============================================================
PROCEDURE testBlock IS
v_long_number_in VARCHAR2(30);
v_long_number VARCHAR2(30);
BEGIN
v_long_number_in := '#D0043676227830464';
IF SUBSTR(v_long_number_in, 1, 4) = '#D00' THEN
v_long_number := SUBSTR(v_long_number_in, 5);
ELSIF SUBSTR(v_long_number_in, 1, 2) = '00' THEN
v_long_number := SUBSTR(v_long_number_in, 3);
ELSE
v_long_number := v_long_number_in;
END IF;
END testBlock;
--==============================================================
FUNCTION TRUNCATE_TABLE(p_table_name IN VARCHAR,p_rerun_limit IN NUMBER, p_sleep_sec IN NUMBER) RETURN NUMBER IS
v_status NUMBER;
v_rerun_ind NUMBER;
v_rerun_counter NUMBER;
v_sql_str VARCHAR2(1000);
v_msg_str VARCHAR2(1000);
v_module_name VARCHAR2(30);
BEGIN
v_module_name := 'TRUNCATE_TABLE';
v_rerun_counter := 1;
v_rerun_ind := 1;
v_sql_str := 'TRUNCATE TABLE '||p_table_name;
write_sga_w_log(v_module_name,'Before Execution of :'||v_sql_str);
WHILE v_rerun_ind = 1 LOOP
BEGIN
EXECUTE IMMEDIATE v_sql_str;
v_rerun_ind := 0;
v_status := 0;
EXCEPTION
WHEN OTHERS THEN
v_msg_str := 'Attempt #'||TO_CHAR(v_rerun_counter)||' Failed. Oracle Error: '||SQLERRM;
write_sga_w_log(v_module_name,v_msg_str);
DBMS_LOCK.sleep(p_sleep_sec);
v_rerun_counter := v_rerun_counter + 1;
v_status := -1;
IF v_rerun_counter > p_rerun_limit THEN
v_rerun_ind := 0;
END IF;
END;
END LOOP;
v_msg_str := 'Procedure Finished with Status : '||TO_CHAR(v_status) ||' After '||TO_CHAR(v_rerun_counter)||' Attempts.';
write_sga_w_log(v_module_name,v_msg_str);
RETURN v_status;
EXCEPTION
WHEN OTHERS THEN
v_msg_str := 'Unexpected Exception in Procedure '||v_module_name||'. Error Details: '||SQLERRM;
write_sga_w_log(v_module_name,v_msg_str);
v_status := -1;
RETURN v_status;
END TRUNCATE_TABLE;
PROCEDURE purgeOldData (p_table_name IN VARCHAR2, p_days_to_keep IN NUMBER) IS
v_table_name VARCHAR2(30);
v_table_backup VARCHAR2(30);
v_sql_str VARCHAR2(1000);
v_msg_text VARCHAR2(1000);
v_module_name VARCHAR2(30);
v_status NUMBER;
v_time_stamp_field VARCHAR2(30);
v_step VARCHAR2(60);
BEGIN
v_module_name := 'ADMIN_UTIL.purgeOldData';
v_table_name := p_table_name;
v_table_backup := SUBSTR(p_table_name,1,26)||'_BAK';
IF v_table_name = 'TEST_DBA_TABLES' THEN
v_time_stamp_field := 'last_analyzed';
ELSIF v_table_name = 'IPN_IBR_CELL_REPORT_DATA' THEN
v_time_stamp_field := 'ts_start';
ELSE
v_sql_str := 'Unknown table in purgeOldData code. Please Edit procedure!!! Terminating Execution';
WRITE_SGA_W_LOG(v_module_name,v_sql_str);
RETURN;
END IF;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'DROP TABLE '||v_table_backup;
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'CREATE TABLE '||v_table_backup||' AS SELECT * FROM '||v_table_name||' WHERE 1=2';
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'ALTER TABLE '||v_table_backup||' NOLOGGING';
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'INSERT /*+ APPEND */ INTO '||v_table_backup||' SELECT * FROM '||v_table_name||' WHERE '||v_table_name||'.'||v_time_stamp_field||' > SYSDATE - '||TO_CHAR(p_days_to_keep);
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'TRUNCATE TABLE '||v_table_name;
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
v_status := TRUNCATE_TABLE(v_table_name,10,10);
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'INSERT /*+ APPEND */ INTO '||p_table_name||' SELECT * FROM '||v_table_backup;
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
RAISE;
END;
BEGIN
DBMS_LOCK.sleep(2);
v_sql_str := 'DROP TABLE '||v_table_backup;
v_step :=SUBSTR(v_sql_str,1,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXECUTE IMMEDIATE v_sql_str;
WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');
EXCEPTION
WHEN OTHERS THEN
WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');
END;
DBMS_LOCK.sleep(2);
v_sql_str := 'PURGE_OLD_DATA From Table '||v_table_name||' is Completed';
v_step :=SUBSTR(v_sql_str,60);
WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Error in procedure '||v_module_name||' in step: '||v_step||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
DBMS_OUTPUT.put_line(v_msg_text );
END purgeOldData;
END ADMIN_UTIL;
permissions
GRANT CREATE TABLE TO XXX;
create job
DECLARE
v_job_number NUMBER(10);
BEGIN
DBMS_JOB.SUBMIT (JOB => v_job_number,
WHAT => 'ADMIN_UTIL.purgeOldData (''IPN_IBR_CELL_REPORT_DATA'', 2);',
NEXT_DATE => TRUNC(SYSDATE + 1)+6/24+30/1440,
INTERVAL => 'TRUNC(SYSDATE + 1)+6/24+30/1440'
);
COMMIT;
END;
/
No comments:
Post a Comment