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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google