For NVARCHAR2 and VARCHAR2 maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. This is useful you have to allocate more data to a variable.
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 not 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 be 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;
What are your thoughts on this post?
I’d love to hear from you! Click this link to email me—I reply to every message!
Also use the share button below if you liked this post. It makes me smile, when I see it.