Monitoring Jobs

Submitted by dave on Fri, 10/03/2008 - 16:53

DBMS_JOBs (8i,9i)

Creating

Modifying

Deleting

Status

Job Definitions

SELECT * FROM DBA_JOBS;

Currently Running Jobs

select v$session.sid, serial#, JOB
from v$session, dba_jobs_running
where v$session.sid = dba_jobs_running.sid;

SCHEDULED_JOBs (10g+)

Creating

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'QIPMU.DO_PURGE',
job_type=>'STORED_PROCEDURE',
job_action=>'QIPMU.SMART_VIEWS_PKG.SP_PURGETABLES',
start_date=>trunc(sysdate+7,'D')+18/24,
enabled=>true,
repeat_interval => 'trunc(sysdate+7,''D'')+18/24',
comments => 'Weekly log purge'
);
END;
/

Modifying

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'QIPMU.DO_PURGE',
attribute => 'MAX_FAILURES',
value => 3);
END;
/

Deleting

BEGIN
DBMS_SCHEDULER.DROP_JOB('QIPMU.DO_PURGE');
END;
/

Status

select * from dba_scheduler_jobs;

select * from dba_scheduler_running_jobs;

SELECT max_failures, MAX_FAIL,
job_priority,
schedule_limit SCHED_LIMIT,
logging_level
FROM user_scheduler_jobs;

select * from dba_scheduler_job_log
order by log_date desc;

select job_name, status, error#, run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS;