Here I am listing the commands using for creating, running, and fetching the scheduler jobs in Oracle database for easy reference.
Start, Stop, and Delete jobs
Jobs can run and stop manually using RUN_JOB and STOP_JOB procedures. The use_current_session argument of RUN_JOB determines whether a job runs synchronously or asynchronously.
BEGIN
-- Run job synchronously.
DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'JOB_NAME',
use_current_session => FALSE);
-- Stop jobs.
DBMS_SCHEDULER.STOP_JOB (JOB_NAME => 'JOB_NAME', 'second_JOB_NAME');
END;
STOP_JOB can shutdown jobs gracefully, but there are times when graceful shutdown is possible. In that case you can use FORCE=> TRUE as below.
BEGIN
-- to stop jobs forcefully.
DBMS_SCHEDULER.stop_job (JOB_NAME => 'JOB_NAME', FORCE=> TRUE);
END;
Jobs can delete using DROP_JOB.
BEGIN
-- to delete jobs.
DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'JOB_NAME');
END;
Enable and Disable jobs
Jobs can enable using ENABLE and disable using DISABLE parameter.
BEGIN
-- to disable jobs.
DBMS_SCHEDULER.ENABLE ('JOB_NAME');
END;
BEGIN
-- to disable jobs.
DBMS_SCHEDULER.DISABLE ('JOB_NAME');
END;
Listing the jobs
ALL_SCHEDULER_JOBS displays information about the Scheduler jobs accessible to the current user.
SELECT * FROM ALL_SCHEDULER_JOBS;
DBA_SCHEDULER_JOBS displays information about all Scheduler jobs in the database.
SELECT * FROM DBA_SCHEDULER_JOBS;
USER_SCHEDULER_JOBS displays information about the Scheduler jobs owned by the current user. This view does not display the OWNER column.
SELECT * FROM USER_SCHEDULER_JOBS;
PL SQL Query to list the currently running jobs
SELECT job_name, session_id, running_instance, elapsed_time, cpu_used
FROM dba_scheduler_running_jobs;
-- or
SELECT job_name, session_id, running_instance, elapsed_time, cpu_used
FROM user_scheduler_running_jobs;
To view the jobs history
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used
FROM dba_scheduler_job_run_details;
-- or
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used
FROM user_scheduler_job_run_details;
Use the share button below if you liked it.
It makes me smile, when I see it.