How to kill a DBMS_JOB that wont die

First I'd like to point out that if you are running Oracle 10G and above you should use scheduler jobs (DBMS_SCHEDULER) rather than the old style jobs(DBMS_JOBS) I am mentioning here.

In the case you can't avoid using DBMS_JOB and your job somehow gets stuck and won't complete, and is at this point causing lots of nasty problems on your database; this is how you kill it.

1. Find the job id. You must be logged in as a DBA user.

select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

2. login as the user running the job and break the job. Be sure to commit after break and use the above query to see that it's broken

BEGIN
DBMS_JOB.BROKEN(4750, TRUE);
END;
/

commit;

3. login a a DBA user and run "show parameter job;" (note the number of processes)

4. Run the following stop all jobs (this includes shceduler jobs) "alter system set JOB_QUEUE_PROCESSES=0;"

5. Remove the job from the queue.

BEGIN
DBMS_JOB.remove(4750);
END;
/

commit;

6. Set the JOB_QUEUE_PROCESSES parameter back to it's original state so jobs can run.