isitdevops.com/databases

SET ECHO off
REM NAME:   TFSDBJRN.SQL
REM USAGE:”@path/tfsdbjrn”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on DBA_JOBS, DBA_JOBS_RUNNING
REM ————————————————————————
REM PURPOSE:
REM    Lists all jobs that are currently running in the local databbase.
REM ————————————————————————
REM Main text of script follows:
 
set pagesize 80
ttitle –
  center  ‘List Running Jobs’  skip 2
 
col sess format 99   heading ‘Ses’
col jid  format 999  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 ‘This|Run|Date’
col nrt  format a5   heading ‘This|Run|Time’
col fail format 99 heading ‘Err’
 
select
  djr.sid                        sess,
  djr.job                        jid,
  dj.log_user                    subu,
  dj.priv_user                   secd,
  dj.what                        proc,
  to_char(djr.last_date,’MM/DD’) lsd,
  substr(djr.last_sec,1,5)       lst,
  to_char(djr.this_date,’MM/DD’) nrd,
  substr(djr.this_sec,1,5)       nrt,
  djr.failures                   fail
from
  sys.dba_jobs dj,
  sys.dba_jobs_running djr
where
  djr.job = dj.job
/