I’m just getting started in the world of Oracle Jobs, but here’s a nice little tip I had to look up today. You can stop a job by calling the REMOVE(job_id) function of the DBMS_JOB package. Notice there is no ‘S’ after DBMS_JOB, a minor mistake that I already made today.

So if I have a job 10045 and would like to get rid of it I would call:

begin
DBMS_JOB.REMOVE(10045);
end;

Also, too check get an idea of how much longer an existing job has to run you can query the session table:

SELECT round(sofar*100 / totalwork) as percent_done,
       round((totalwork - sofar) * (((last_update_time - start_time) *
       24*60)/sofar)) as min_remaining,
       round(elapsed_seconds/60) as elapsed_time
FROM v$session_longops a
WHERE SID = {the SID running the job}

You can find the SID of the running job using the following query:

select * from v$session
where status=‘ACTIVE’

And look for the username that is running the query.

This is very elementary stuff that a lot of programmers probably already know, but still worth mentioning. I’ve also been doing some work the the DBMS_APPLICATION_INFO package which lets your application update its status for others to see.