Friday, March 17, 2017

How to get Scheduled jobs information in Oracle?

SQL> select TABLE_NAME from dict where table_name like 'DBA_SCHEDULER_JOB%';

DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_JOB_DESTS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_ROLES
DBA_SCHEDULER_JOB_RUN_DETAILS

SQL> select TABLE_NAME from dict where table_name like 'ALL_SCHEDULER_JOB%';

ALL_SCHEDULER_JOBS
ALL_SCHEDULER_JOB_ARGS
ALL_SCHEDULER_JOB_CLASSES
ALL_SCHEDULER_JOB_DESTS
ALL_SCHEDULER_JOB_LOG
ALL_SCHEDULER_JOB_RUN_DETAILS



To See All Scheduled Jobs

 select owner, job_name, state from all_scheduler_jobs;



To Check all scheduled jobs for a particular user

select owner, job_name, state from all_scheduler_jobs where owner ='XXX';


To See Job Run Details

 select LOG_ID, LOG_DATE, JOB_NAME, STATUS from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name ='JOB_AUTOCLOSE_QUEUE';


Enable / Disable a job

BEGIN
  DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
  DBMS_SCHEDULER.DISABLE('myjob');
END;



The DBA views are restricted. So you won't be able to query them unless you're connected as a DBA or similarly privileged user.
The ALL views show you the information you're allowed to see. Normally that would be jobs you've submitted, unless you have additional privileges.



To extract DDL of Scheduled Job

select dbms_metadata.get_ddl('PROCOBJ', 'JOB_AUTOCLOSE_QUEUE') from dual;

No comments:

Post a Comment