Oracle Spin – Flimatech Blog

Sharing Our Database Experience

Posts Tagged ‘dbms_ijob’

Using sys.dbms_iob one can manage jobs in dba_jobs.

Posted by Amin Jaffer on March 11, 2012

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS.

Here are some of the functions available in sys.dbms_ijob.
To execute/run job: You don’t have to be an owner of the job
SQL> exec;

If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it and you will get the following error “ORA-23421: job number 21 is not a job in the job queue”

To enable/disable job:
-- to disable a job. Note one should set this when the job is not running by checking DBA_JOBS_RUNNING, because if the job is running you will have to wait till it finishes before disabling it and it has to be followed by a commit.
SQL> exec sys.dbms_ijob.broken(5, true);
SQL> commit;

-- Note the next_date value it initialized when the job is broken.
SQL> select broken, next_date from dba_jobs where job = 5;

- ——————–
Y 01-JAN-4000 00:00:00

-- to enable a job, note it has to be followed by a commit and after the job is enabled the NEXT_DATE is updated based on the value of interval.
SQL> exec sys.dbms_ijob.broken(5, false);
SQL> commit;

SQL> select broken, next_date from dba_jobs where job in ( 5 );

- ——————–
N 11-MAR-2012 19:31:45

To change the next date the job runs, again note if the job is running when one modifies this, it will get reset to the next interval instead of the value specified.
SQL> exec sys.dbms_ijob.next_date(5, sysdate + 1);
SQL commit;

SQL> select next_date from dba_jobs where job in ( 5 );

- ——————–
N 12-MAR-2012 19:41:27

To change the interval
Example below changes the interval to run every Monday at midnight
SQL> exec sys.dbms_ijob.interval(5, 'TRUNC(NEXT_DAY(sysdate, ''MONDAY''))');
PL/SQL procedure successfully completed.
SQL> commit;

Posted in General DBA, Scheduler | Tagged: , , , , | Leave a Comment »


Get every new post delivered to your Inbox.

Join 37 other followers