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;