How can a DBMS_JOB mark itself as broken?
I want to run an update on a large list of rows in batches of 1000 rows. When putting the update statement inside some periodically executed DBMS_JOB is there a way for the job to suspend itself after the number of affected rows becomes zero?
Can a job find out its own job ID so it can call DBMS_JOB.Broken()?
select JOB from all_jobs where what like '%YOUR_JOB_PROCEDURE%';
Also, I recommend to use DBMS_SCHEDULER instead of DBMS_JOB, if it is also available in your Oracle version http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm
JOB ID is JOB column:
select * from dba_jobs
Current running jobs:
select dbr.sid, s.serial#, s.username, dbj.* from dba_jobs_running dbr, dba_jobs dbj , v$session s where dbr.job=dbj.job and s.sid=dbr.sid;