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

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World

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

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer