General
=====================================
For a job connecting to a remote DB via a db_link, sometimes due to network issues, the job is in "stuck" mode
as a workaround, there is a job running every 2 hours, and killing jobs that run over 1 hour.
=====================================
PL/SQL CODE
=====================================
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS
-----------------------------------------------
-- Known Exceptions
-----------------------------------------------
EXP_ORA_SESS_MARK_FOR_KILL EXCEPTION;
PRAGMA EXCEPTION_INIT(EXP_ORA_SESS_MARK_FOR_KILL,-31);
-------------------------------------------------
PROCEDURE write_sga_w_log(p_procedure_name IN VARCHAR2,
p_data IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG (module_name, msg_text, msg_date)
VALUES (p_procedure_name, p_data, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END write_sga_w_log;
-------------------------------------------------
PROCEDURE KILL_LONG_RUNNING_JOB IS
v_module_name SGA_W_LOG.module_name%TYPE;
v_msg_text SGA_W_LOG.msg_text%TYPE;
v_sql_cmd VARCHAR2(1000);
CURSOR long_running_jobs_cur IS
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||V$SESSION.sid||','||V$SESSION.serial#||'''', 1,15)||' IMMEDIATE' AS kill_cmd,
V$SESSION.sid,
V$SESSION.serial#,
DBA_JOBS.what
FROM DBA_JOBS ,
DBA_JOBS_RUNNING,
V$SESSION
WHERE DBA_JOBS.this_date < SYSDATE - 1/24
AND DBA_JOBS_RUNNING.job = DBA_JOBS.job
AND DBA_JOBS_RUNNING.sid = V$SESSION.sid
AND V$SESSION.status <> 'KILLED';
BEGIN
v_module_name := 'KILL_LONG_RUNNING_JOB';
FOR long_running_jobs_rec IN long_running_jobs_cur LOOP
BEGIN
v_sql_cmd := long_running_jobs_rec.kill_cmd;
v_msg_text := 'Killing Stuck Job :'||long_running_jobs_rec.what;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_msg_text := 'Runing: '||v_sql_cmd;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
EXECUTE IMMEDIATE v_sql_cmd;
EXCEPTION
WHEN EXP_ORA_SESS_MARK_FOR_KILL THEN
v_msg_text := 'Done! Session Was Marked For Kill';
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
END KILL_LONG_RUNNING_JOB;
-------------------------------------------------
PROCEDURE RECREATE_JOB IS
v_module_name SGA_W_LOG.module_name%TYPE;
v_msg_text SGA_W_LOG.msg_text%TYPE;
v_sparx_what VARCHAR2(1000);
CURSOR get_job_list_cur(cp_job_what IN VARCHAR2) IS
SELECT *
FROM DBA_JOBS
WHERE UPPER(WHAT) = cp_job_what;
BEGIN
v_module_name := 'RECREATE_JOB';
v_sparx_what := UPPER('EXT_SPARX.schedule;');
FOR get_job_list_rec IN get_job_list_cur(v_sparx_what) LOOP
v_msg_text := 'Deleting Job :'||get_job_list_rec.job;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
BEGIN
DBMS_JOB.remove(get_job_list_rec.job);
COMMIT;
END;
v_msg_text := 'Creating Job :'||get_job_list_rec.job;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
BEGIN
DBMS_JOB.ISUBMIT
( job => get_job_list_rec.job
,what => get_job_list_rec.what
,next_date => get_job_list_rec.next_date
,interval => get_job_list_rec.interval
);
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
END RECREATE_JOB;
-------------------------------------------------
END;
=====================================
Job
=====================================
DECLARE
v_job_number NUMBER(10);BEGIN
DBMS_JOB.SUBMIT (JOB => v_job_number,
WHAT => 'ADMIN_UTIL.KILL_LONG_RUNNING_JOB;',
NEXT_DATE => TRUNC(SYSDATE,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/1)+121)*1)/(1440),
INTERVAL => 'TRUNC(SYSDATE,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,''MI''))/1)+121)*1)/(1440)'
);
COMMIT;
END;
/
