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); Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of using Oracle RAC with Data Warehouse!!
   A Few Things To Know Regarding Databases
   Enhance performance of Oracle Data Load
   Expert Tips on Drop Temporary Tablespace Hangs!!