Various queries that give us JOBS information

scheduler: JOBS information.sql

--Scheduler JOBS

set lines 200
set pages 500
column OWNER format a15
column SCHEDULE_NAME format a25
column START_DATE format a50
column REPEAT_INTERVAL format a20
SELECT OWNER, JOB_NAME, JOB_STYLE, SCHEDULE_NAME, START_DATE, REPEAT_INTERVAL,  STATE FROM DBA_SCHEDULER_JOBS;


--JOBS
 
set lines 200
set pages 500
column interval format a15
culumn what format a25
select JOB, 
to_char(last_date,'DD-MM-YYYY HH24:MI') last_date, 
to_char(next_date,'DD-MM-YYYY HH24:MI') next_date,
TOTAL_TIME, 
BROKEN, 
FAILURES,
INTERVAL,
WHAT
from dba_jobs 
WHERE JOB = 404;

--Change the next RUN
alter session set current_schema=DARE_PKG;
exec dbms_ijob.NEXT_DATE(404, TO_DATE('26/jul/2016 23:00:00','DD-MON-YY HH24:MI:SS'));
--
-- List info for all jobs currently running.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
SELECT a.job "Job",
       a.sid,
       a.failures "Failures",       
       Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",      
       Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"             
FROM   dba_jobs_running a
/
 
 --specific job information 
 select OWNER,JOB_NAME,JOB_STYLE,JOB_TYPE,JOB_ACTION,SCHEDULE_NAME,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,ENABLED,AUTO_DROP,STATE,LAST_RUN_DURATION, LAST_START_DATE, comments
from dba_scheduler_jobs
where job_name = 'ACHIP_IMPORT_SCHEDULE_JOB';

 --specific job information 2
SELECT
SUBSTR(job,1,6) "Job",
SUBSTR(log_user,1,5) "User",
SUBSTR(schema_user,1,5) "Schema",
SUBSTR(To_CHAR(last_date, 'DD.MM.YYY HH24:MI'),1,16) "Last Date",
SUBSTR(To_CHAR(next_date, 'DD.MM.YYY HH24:MI'),1,16) "Next Date",
SUBSTR(broken,1,2) "B",
SUBSTR(failures,1,8) "Failed",
SUBSTR(what,1,100) "Refresh"
FROM DBA_JOBS
Where what like '%WAFER_PD_25_85_PROBE%';

--disable & enable a DBMS JOB
exec dbms_ijob.broken(404,true);
exec dbms_ijob.broken(404,false);

--get JOB DDL when is a stored procedure
--The trick is to use “PROCOBJ” (stands for ‘procedural object’) as object-type for all scheduler-objects:
SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 132
select dbms_metadata.get_ddl('PROCOBJ','<JOBNAME>','<SCHEMA>') from dual;