Oracle: display jobs from scheduler

Show past jobs:

1 2 3

select log_date, job_name, status, owner from dba_scheduler_job_log where job_name like 'TEST_JOB%' order by log_date desc;

NB: STATE will be set to 'RUNNING' is the job is currently running

Show current jobs:

1 2 3

select start_date, job_name, job_action, job_class, owner, state, repeat_interval from dba_scheduler_jobs where job_name like 'TEST_JOB%' order by start_date desc;

Stop a job: always use owner.job_name

1	exec DBMS_SCHEDULER.stop_job('PROV_DB.MBQT_RZ_SET$1386', true);

Show why a job failed:

1 2 3 4

SELECT LOG_DATE, OWNER, JOB_NAME, STATUS, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'MBQT_%' AND STATUS='FAILED' ORDER BY LOG_DATE DESC;

Show MBQT_SUSPEND jobs + 000 like argument

1 2 3 4 5 6 7 8

select dba_scheduler_jobs.start_date, dba_scheduler_jobs.job_name, dba_scheduler_jobs.job_action, dba_scheduler_jobs.job_class, dba_scheduler_jobs.owner, dba_scheduler_jobs.state, dba_scheduler_jobs.repeat_interval,     dba_scheduler_job_args.value from dba_scheduler_jobs,     dba_scheduler_job_args where dba_scheduler_jobs.job_name like 'MBQT_SUSPEND%' and dba_scheduler_jobs.job_name = dba_scheduler_job_args.job_name and dba_scheduler_job_args.value like '000%' order by dba_scheduler_jobs.start_date desc;

Run scheduled job immediately

1	EXEC dbms_scheduler.run_job('RAF_CONS_DB.MBQT_SUSPEND_CO$15390405');

Then remove this job

1	EXEC dbms_scheduler.drop_job('RAF_CONS_DB.MBQT_SUSPEND_CO$15390405', true);

Remove program

1	SELECT * FROM DBA_SCHEDULER_PROGRAMS where program_name like '%MBQT_%';

1	EXEC dbms_scheduler.drop_program('PROV_DB.MBQT_CO_POST');