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
   Exceptional Tips for Exceptions in Oracle
   DBA Tips for Killing Oracle Idle Session!!
   Handling Tricky Oracle Job Scheduling Problem!!
   Secrets of tracking historical changes made to Oracle Tables!!