How to Check Oracle 9i, 10g, 11g Scheduler Jobs Status

advertisements

_____________________________________________________________________________________________________________________

Oracle 9i and above you can use following query from the DBA_JOBS view to get the details like description, next execution, last successful execution etc.

SQL>SELECT JOB, SUBSTR(WHAT,1,45), LAST_DATE, NEXT_DATE, NEXT_SEC, BROKEN FROM DBA_JOBS;

       JOB SUBSTR(WHAT,1,45)                             LAST_DATE           NEXT_DATE           NEXT_SEC B

---------- --------------------------------------------- ------------------- ------------------- -------- -

        41 pk_cost.p_start_cost_batch;                   2014-01-30 11:35:30 2014-01-30 11:55:30 11:55:30 N

        48 DBMS_JOB.NEXT_DATE ( 41,to_date(sysdate + 5/4 2014-01-29 23:44:17 2014-01-30 23:44:17 23:44:17 N

DBA_JOBS_RUNNING lists all jobs that are currently running in the instance.
Oracle 10g and above: ALL_SCHEDULER_JOB_RUN_DETAILS displays log run details for the Scheduler jobs accessible to the current user.

SQL> select log_id, log_date, job_name, status, error#, additional_info

  from dba_scheduler_job_run_details where owner='STHOMAS'; 
    LOG_ID LOG_DATE
---------- ---------------------------------------------------------------------------
JOB_NAME                                                          STATUS                             ERROR#
----------------------------------------------------------------- --------------------
ADDITIONAL_INFO
---------------------------------------------------------------------------------------------

     50484 29-JAN-14 11.37.10.371266 AM +01:00

TABLEEXPORT                                                       FAILED                                 13
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied

set pages 1000 lines 120

col job_name for a30

col status for a10

col run_duration for a20

select JOB_NAME, STATUS, RUN_DURATION, LOG_DATE, ACTUAL_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS

where job_name like '%AT_SA_SPC_SY%' order by LOG_DATE;

JOB_NAME                       STATUS     RUN_DURATION         LOG_DATE             ACTUAL_START_DATE

------------------------------ ---------- -------------------- -------------------- --------------------

ORA$AT_SA_SPC_SY_2             SUCCEEDED  +000 00:00:02        09-DEC-13 10.00.10.4 09-DEC-13 10.00.08.5

                                                               13735 PM -08:00      00514 PM PST8PDT

ORA$AT_SA_SPC_SY_5             SUCCEEDED  +000 00:00:03        10-DEC-13 10.00.08.0 10-DEC-13 10.00.04.6

                                                               63156 PM -08:00      20684 PM PST8PDT

_____________________________________________________________________________________________________________________

Website Stats