Monitoring and Maintainence of Jobs

Display database jobs and schedulder jobs:-

set lines 200
Set pages 200
col WHAT for a40
select JOB,WHAT,BROKEN,FAILURES from dba_jobs;


col OWNER for a15
col STATE for a10
col FAL for 999
select OWNER,JOB_NAME,STATE,FAILURE_COUNT FAL from dba_scheduler_jobs;


Displays scheduler information about job programs.

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40

SELECT owner,
       program_name,
       program_type,
       program_action,
       number_of_arguments,
       enabled,
       comments
FROM   dba_scheduler_programs
ORDER BY owner, program_name;


Displays scheduler information about job schedules

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40

SELECT owner,
       schedule_name,
       start_date,
       repeat_interval,
       end_date,
       comments
FROM   dba_scheduler_schedules
ORDER BY owner, schedule_name;


Displays scheduler information for running jobs.

SET LINESIZE 200

COLUMN owner FORMAT A20

SELECT owner,
       job_name,
       running_instance,
       elapsed_time
FROM   dba_scheduler_running_jobs
ORDER BY owner, job_name;

select * from dba_jobs_running;

--To check stats job history for 10G

set linesize 300
col JNM for a30
col JST for a25
col RUN_DURATION for a15
col SLAVE_PID for 99999
col STATUS for a10
select TO_CHAR(ACTUAL_START_DATE, 'YYYY-MM-DD_HH24:MI:SS') jst, ERROR#, JOB_NAME jnm, STATUS,SESSION_ID,RUN_DURATION,SLAVE_PID
from DBA_SCHEDULER_JOB_RUN_DETAILS  where JOB_NAME = 'GATHER_STATS_JOB' order by ACTUAL_START_DATE;

--To check stats job history for 11G

set linesize 300
col CNM for a32
col JOB_DURATION for a15
col jst for a20
col JOB_STATUS for a13

select CLIENT_NAME, STATUS from dba_autotask_client where client_name = 'auto optimizer stats collection';

select TO_CHAR(JOB_START_TIME, 'YYYY-MM-DD_HH24:MI:SS') jst, JOB_ERROR, CLIENT_NAME cnm, JOB_STATUS,JOB_DURATION
from dba_autotask_job_history  where client_name = 'auto optimizer stats collection' order by job_start_time;