Removing Jobs from Job Queue

Great Tips for Removing Jobs from Job Queue Automatically! !

Author: Burleson

Today we will discuss some great tips that will help you automatically remove jobs from the job queue. You can either use dbms_job in Oracle 9i or dbms_scheduler in Oracle 10g above to manage the scheduled jobs. The dbms_job is outdated by dbms_scheduler that offers lots more features.

Automatically Removing Scheduled Jobs from Job Queue:
The DBMS_JOB subprograms can be used to schedule and manage jobs in the job queue. The REMOVE procedure of DBMS_JOB subprograms can be used to remove an existing job from the job queue. However it does not stop a running job. Use dbms_job.remove( job => number) syntax to remove scheduled job from the job queue.
exec dbms_job.remove( 1213);

Automatically Removing Running Jobs from Job Queue:
In order to automatically removing running jobs from Job Queue you must first ensure that you want to abort a running task as for this purpose the rollback will be invoked. A simple shell script can be used to remove all running scheduled jobs.

Capture the SID and PID:
The v$session view represents the sessions currently connected to the instance whereas v$sysstat shows the summary of resource usage First of all we need to capture the SID and PID for all running jobs (v$session, v$sysstat).

Break Jobs:
Then we will break all jobs by using below syntax

EXEC DBMS_JOB.BROKEN( job#,TRUE) ;

Kill Session:
Finally we will kill the session for each (SID, PID) of the running jobs. You need to issue below command for this purpose

ALTER SYSTEM KILL SESSION 'sid,serial# ';

Automatically Removing Queued Jobs from Job Queue:
Below steps discuss how queued jobs can be removed from the job queue.

Stop all job execution:
First of all we will stop all job execution by running below command.

ALTER SYSTEM SET job_queue_processes = 0;

Find Jobs in Queue:
DBA_JOBS describes all jobs in the database. Now we will use DBA_JOBS to find the queued jobs.

Break Jobs:
You can use DBMS_JOB package to schedule a job to run at a specified time. Now we will break the jobs by using DBMS_JOBS.

Remove Jobs:
Finally we will remove the jobs by using the REMOVE procedure of DBMS_JOBS package.

exec dbms_job.remove( 1213);