Question: Is there a way to automatically remove jobs from the job queue?  Also, can I remove a job after it is finished executing?  What about executing jobs?  Can I remove those jobs?

Answer:  Depending on your release of Oracle, you can use dbms_job (Oracle 9i) or dbms_scheduler (10g and beyond) to manage scheduled jobs.  (dbms_job is superseded by dbms_scheduler, with lots more features. . . . )

Also see the advanced queuing tools, since that governs the queues, the dbms_aqadm and dbms_aq packages, plus Dr. Halls dba_jobs scripts from his book "Oracle Job Scheduling".

Removing Scheduled jobs

Scheduled job can be easily removed using DBMS_JOB.REMOVE(JOB => nn)
 

Removing Running jobs

For running jobs, first, make sure that you carefully check to make sure that you want to abort a running task (the rollback will be invoked).  To remove all running scheduled jobs, you could write a shell script to:

1 - Capture the SID and PID for all running jobs (v$session, v$sysstat).  See code depot for complete scripts.

2 - Break all jobs (EXEC DBMS_JOB.BROKEN(job#,TRUE);

3 - For each (SID, PID), issue an ALTER SYTEM KILL SESSION command (ALTER SYSTEM KILL SESSION 'sid,serial#';)
 

Removing Queued jobs

Again, for complete details, see Dr. Halls book "Oracle Job Scheduling":

1 - Stop all job execution: (ALTER SYSTEM SET job_queue_processes = 0;)

2 - Find all queued jobs (using dba_jobs)

3 - Break the jobs (using dbms_jobs)

4 - Remove the jobs (using dbms_jobs: DBMS_JOB.REMOVE(JOB => nn)


 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.