DBMS_JOB

For job scheduling Oracle provide plsql package called DBMS_JOB/DBMS_SCHDULER.
Before Oracle 10g we use DBMS_JOB for job scheduling through ORACLE.

Simple Demostration of DBMS_JOB package.

Note

1.There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue.

SQL> drop table master purge;

Table dropped.

SQL> create table master
2 ( no number default '0',
3 ddate date default sysdate,
4 name varchar2(20) default 'NONE');

Table created.

SQL>create sequence MASTER_SEQ;

sequence created.

SQL>select master_seq.nextval from dual;

x
-----
1

SQL> create or replace procedure insert_master
2 is
3 begin
4 insert into master values ( master_seq.nextval, default, default);
5 commit;
6* end;
7 /

Procedure created.

SQL> show err
No errors.

SQL>variable jobno number;

SQL> ed
Wrote file afiedt.buf

1 begin
2 dbms_job.submit ( :jobno,
3 what => 'BEGIN insert_master; END;',
4 next_date => SYSDATE,
5 interval => 'SYSDATE +1/1440');
6 commit;
7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> print jobno

JOBNO
----------
29


SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select * from master;

NO DDATE NAME
---------- -------------------- --------------------
0 16-apr-2007 09:44:26 NONE
0 16-apr-2007 09:45:27 NONE
0 16-apr-2007 09:46:29 NONE
0 16-apr-2007 09:47:30 NONE
0 16-apr-2007 09:48:31 NONE
2 16-apr-2007 09:49:33 NONE
3 16-apr-2007 09:50:34 NONE
4 16-apr-2007 09:51:36 NONE
5 16-apr-2007 09:52:37 NONE
6 16-apr-2007 09:53:39 NONE

10 rows selected.

Above job run EVERY MINUTE.through below view we can see job status.
1.USER_JOBS / DBA_JOBS

column log_user format a20
column priv_user format a20
column schema_user format a20

SQL> select job,log_user,priv_user,schema_user
2 from user_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER
---------- -------------------- -------------------- --------------------
27 TAJ TAJ TAJ
28 TAJ TAJ TAJ
29 TAJ TAJ TAJ

SQL> select last_date, last_sec,
2 this_date, this_sec,
3 next_date,next_sec,
4 total_time
5 from user_jobs;

LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME
--------- -------- --------- -------- --------- -------- ----------
16-APR-07 02:29:03 01-JAN-00 00:00:00 1
16-APR-07 02:03:32 01-JAN-00 00:00:00 2
16-APR-07 10:00:49 16-APR-07 10:01:49 0

Note: TOTAL_TIME --->
Total wall clock time spent by the system on this job, in seconds

SQL> column what format a20
SQL> column interval format a20
SQL> select what , interval, failures, broken
2 from user_jobs;

WHAT INTERVAL FAILURES B
-------------------- -------------------- ---------- -
BEGIN pro_t; END; sysdate+1/48 16 Y
BEGIN pro_t; END; SYSDATE+1/48 16 Y
BEGIN insert_master; SYSDATE +1/1440 0 N
END;

NOTE : Job Intervals and Date Arithmetic

Every Second.
INTERVAL => 'SYSDATE +1/86400';

1.Every Minute.
interval => 'SYSDATE +1/1440';

2.Every Ten Minute.
interval => 'SYSDATE +10/1440';

3.Every Half Hour.
interval => 'SYSDATE +30/1440';

4.Every Hour.
interval => 'SYSDATE +60/1440';

5.Every Day.
interval => 'SYSDATE +1';

6.Every Day with Fixed Time. ex: every night 8:00pm.
interval => 'trunc(SYSDATE +1) + 20/24'

7.Every Midnight 12:00.
interval => 'trunc(SYSDATE +1)'

8.Every Week
interval => 'trunc(SYSDATE +7)'

9.Every Month
interval => 'add_months(SYSDATE, 1)'


REMOVE procedure.

Removes a specified job from the job queue.

SQL> select job,schema_user from user_jobs;

JOB SCHEMA_USER
---------- --------------------
27 TAJ
28 TAJ
29 TAJ

SQL> begin
2 dbms_job.REMOVE('27');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select job,schema_user from user_jobs;

JOB SCHEMA_USER
---------- --------------------
28 TAJ
29 TAJ

CHANGE procedure

Alters a specified job that has already been submitted to the job queue. You can alter the job description, the time at which the job will be run, or the interval between executions of the job.

Restriction:

You can alter only jobs that you own.

SQL> @C:\NLS.SQL

Session altered.

SQL> select what , next_date, interval
2 from user_jobs
3 where job = '29';

WHAT NEXT_DATE INTERVAL
-------------------- -------------------- --------------------
BEGIN insert_master; 16-apr-2007 12:03:40 SYSDATE +1/1440
END;

SQL> begin
2 DBMS_JOB.CHANGE(29,
3 'begin delete_master; end;',
4 SYSDATE,
5 'sysdate +2/1440');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select what , next_date, interval
2 from user_jobs
3 where job = '29';

WHAT NEXT_DATE INTERVAL
-------------------- -------------------- --------------------
begin delete_master; 16-apr-2007 12:04:58 sysdate +2/1440
end;

WHAT procedure

Alters the job description for a specified job


NEXT_DATE procedure

Alters the next execution time for a specified job


INTERVAL procedure

Alters the interval between executions for a specified job


BROKEN procedure

Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it.

SQL> select * from master ;

NO DDATE NAME
---------- -------------------- --------------------
156 16-apr-2007 12:41:51 NONE
154 16-apr-2007 12:39:48 NONE
155 16-apr-2007 12:40:49 NONE
157 16-apr-2007 12:42:52 NONE

SQL> select broken from user_jobs where job=29;

B
-
N

SQL> begin
2 dbms_job.broken(29,TRUE);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select broken from user_jobs where job=29;

B
-
Y

SQL> select sysdate,master.* from master;

SYSDATE NO DDATE NAME
-------------------- ---------- -------------------- --------------------
16-apr-2007 12:45:42 156 16-apr-2007 12:41:51 NONE
16-apr-2007 12:45:42 158 16-apr-2007 12:43:53 NONE
16-apr-2007 12:45:42 154 16-apr-2007 12:39:48 NONE
16-apr-2007 12:45:42 155 16-apr-2007 12:40:49 NONE
16-apr-2007 12:45:42 157 16-apr-2007 12:42:52 NONE

SQL> --again ENABLE job.
SQL> begin
2 dbms_job.broken(29,FALSE);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select broken from user_jobs where job=29;

B
-
N

SQL> select sysdate,master.* from master;

SYSDATE NO DDATE NAME
-------------------- ---------- -------------------- --------------------
16-apr-2007 12:54:48 160 16-apr-2007 12:52:31 NONE
16-apr-2007 12:54:48 161 16-apr-2007 12:53:32 NONE
16-apr-2007 12:54:48 156 16-apr-2007 12:41:51 NONE
16-apr-2007 12:54:48 159 16-apr-2007 12:51:28 NONE
16-apr-2007 12:54:48 162 16-apr-2007 12:54:33 NONE
16-apr-2007 12:54:48 158 16-apr-2007 12:43:53 NONE
16-apr-2007 12:54:48 154 16-apr-2007 12:39:48 NONE
16-apr-2007 12:54:48 155 16-apr-2007 12:40:49 NONE
16-apr-2007 12:54:48 157 16-apr-2007 12:42:52 NONE

9 rows selected.

Note:

Running Broken Jobs
If a problem has caused a job to fail 16 times, Oracle marks the job as broken. Once you have fixed this problem, you can run the job by either:

Forcing the job to run by calling DBMS_JOB.RUN
Marking the job as not broken by calling DBMS_JOB.BROKEN and waiting for Oracle to execute the job
If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the job immediately. If the job succeeds, then Oracle labels the job as not broken and resets its count of the number of failed executions for the job to zero.

Once you reset a job's broken flag (by calling either RUN or BROKEN), job execution resumes according to the scheduled execution intervals set for the job.



RUN procedure

Forces a specified job to run.

SQL> select failures from user_jobs where job = 29;

FAILURES
----------
0

SQL> select failures from user_jobs where job = 28;

FAILURES
----------
16

SQL> begin
2 dbms_job.run(28);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 2

SQL> --Job is not working and already failures reach 16 then job is
SQL> --mark broken.
SQL> select broken,failures from user_jobs where job=28;

B FAILURES
- ----------
Y 17

SQL> column what format a20
SQL> column interval format a20
SQL> --Check WHAT procedure in dbms_job.
SQL> select what from user_jobs where job=28;

WHAT
--------------------
BEGIN pro_t; END;

SQL> --check pro_t procedure.
SQL> select status from user_objects where object_name = 'PRO_T';

STATUS
-------
INVALID

SQL> --Recreate PRO_T procedure.
SQL> select status from user_objects where object_name = 'PRO_T';

STATUS
-------
VALID

SQL> --Try to run force fully
SQL> begin
2 dbms_job.run(28);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select broken,failures from user_jobs where job=28;

B FAILURES
- ----------
N 0

Restrictions:


The procedure RUN contains an implicit commit. Once you execute a job using RUN, you cannot roll back.


Terminating a Job