Cheeky Cockatoo

Life Advice and Stories, Travel, Oracle Tech, Good Food and More

job_list.sql

Download Script

SET ECHO off
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_JOBS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Lists all jobs that have been submitted to run in the
REM    local database job queue.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM   List Submitted Jobs
REM
REM                                        Last  Last  Next  Next
REM                                        Ok    Ok    Run   Run
REM    Id Submitter Security Job           Date  Time  Date  Time Err Ok
REM    -- --------- -------- ------------- ----- ----- ----- ----- --- -
REM    42 KELLY     KELLY    dbms_refresh. 05/17 16:02 01/01 00:00  16 N
REM                           refresh ('"K
REM                          ELLY"."SNAP_
REM                          MASTER"');
REM
REM    45 MATT      MATT     dbms_refresh. 05/22 12:24 01/01 00:00  16 N
REM                          refresh ('"M
REM                          ATT"."SNAP_M
REM                          ASTER"');
REM
REM Main text of script follows:

prompt List Submitted Jobs
prompt
prompt

col jid  format 9999  heading 'Id'
col subu format a10  heading 'Submitter'     trunc
col secd format a10  heading 'Security'      trunc
col proc format a20  heading 'Job'           word_wrapped
col lsd  format a5   heading 'Last|Ok|Date'
col lst  format a5   heading 'Last|Ok|Time'
col nrd  format a5   heading 'Next|Run|Date'
col nrt  format a5   heading 'Next|Run|Time'
col fail format 999  heading 'Errs'
col ok   format a2   heading 'Ok'

select
  job                        jid,
  log_user                   subu,
  priv_user                  secd,
  what                       proc,
  to_char(last_date,'MM/DD') lsd,
  substr(last_sec,1,5)       lst,
  to_char(next_date,'MM/DD') nrd,
  substr(next_sec,1,5)       nrt,
  failures fail,
  decode(broken,'Y','N','Y') ok
from
  sys.dba_jobs
  order by log_user, job
/