Show Jobs

--==================================================================
--
-- Title       :   showjobs.sql
-- Description :   Shows details of all DBMS jobs in the job queue.
--
-- Usage/Notes :
--
-- Copyright   :   ABCdba.com 2002
--
--==================================================================
 
SET TERMOUT OFF
STORE SET save_env.sql REPLACE
SET TERMOUT ON
 
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
SET PAGESIZE 20
SET WRAP ON
SET FEEDBACK OFF
 
COL jobno HEA 'Job' FOR 999;
COL log_user HEA ' User ' FOR a6;
COL success_date HEA ' Last Completed Run ' FOR a20;
 
COL duration HEA ' Duration ' for a10;
COL due_date HEA ' Next Scheduled Run ' FOR a20;
COL failed HEA ' Failed ' FOR a9
 
COL decode_broken HEA 'Broken' FOR a6;
COL what HEA ' Scheduled Action ' FOR a61;
COL running HEA 'Running' FOR a7;
 
SELECT
   LPAD(job,3) jobno, log_user,
   DECODE( last_date, NULL, '  Never Completed   ',
           LPAD(TO_CHAR(last_date,'DD-MON-YY HH24:MI:SS'),19 )) success_date,
   LPAD(TRUNC(total_time/60),4)||' mins' duration,
   LPAD(TO_CHAR(next_date,'DD-MON-YY HH24:MI:SS'),19) due_date,
   LPAD(TRUNC(failures), 2 )||' times' failed,
   DECODE( broken, 'Y', ' YES  ', 'N', '  NO  ', ' ????' ) decode_broken
FROM
   dba_jobs
ORDER BY
   job
/
 
SELECT
   LPAD(job,3) jobno, log_user, what,
   DECODE( this_date, NULL, '  NO   ', '  YES  ') running
FROM
   dba_jobs
ORDER BY
   job
/
 
@save_env.sql
host rm save_env.sql
SET TERMOUT ON