Explore dbms_scheduler

Use can use below Oracle Dictionary API
 USER_SCHEDULER_JOB_LOG
 USER_SCHEDULER_JOBS

Query to find the History of NetVertex Database Scheduler
SELECT * FROM USER_SCHEDULER_JOB_LOG
WHERE OWNER = ‘SCOTT’
AND TO_CHAR(LOG_DATE,’DD-MON-RR’) = TO_CHAR(SYSDATE,’DD-MON-RR’);

Query to find the LAST and NEXT Execution –Info of Database Scheduler
SELECT JOB_NAME,PROGRAM_NAME,NEXT_RUN_DATE,LAST_RUN_DURATION,MAX_RUN_DURATION,RUN_COUNT,FAILURE_COUNT,COMMENTS
FROM USER_SCHEDULER_JOBS;

Note:-
Use Read Only user SCOTTRO in Manage Engine – Database Connectivity
You can configure the Alarm on status column for Query-1 and FAILURE_COUNT column for Query-2

More information you can refer this guide
http://docs.oracle.com/cd/E18283_01/server.112/e17120/scheduse008.htm

Hands On

set linesize 32767
set pagesize 10000
COLUMN JOB_NAME          FORMAT A25
COLUMN NEXT_RUN_DATE     FORMAT A20
COLUMN LAST_RUN_DURATION FORMAT A20
COLUMN MAX_RUN_DURATION  FORMAT A5
COLUMN RUN_COUNT         FORMAT 999999
COLUMN FAILURE_COUNT     FORMAT 999999
COLUMN COMMENTS          FORMAT A15
SELECT JOB_NAME,NEXT_RUN_DATE,LAST_RUN_DURATION,MAX_RUN_DURATION,RUN_COUNT,FAILURE_COUNT,COMMENTS FROM  USER_SCHEDULER_JOBS;
SELECT
LOG_DATE , OWNER ,JOB_NAME ,JOB_CLASS ,OPERATION ,STATUS
FROM USER_SCHEDULER_JOB_LOG
WHERE JOB_NAME '&1';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s