Skip to Main Content
  • Questions
  • question on job queue.How does it works

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Lulekwa.

Asked: August 07, 2002 - 9:08 am UTC

Last updated: September 04, 2007 - 3:44 pm UTC

Version: 8.17

Viewed 1000+ times

You Asked

Hi Tom,
I want to schedule a job that runs from monday to friday, between 6a.m and 6pm every 15 minutes time.How do I schedule it?
Yes I am using dbms_job.

I am checking

if to_number(to_char(sysdate,'hh24mi')) between 0600 and 1800 then
dbms_job.submit(job_no,job_name,
next_day(sysdate + 15/1440,'MONDAY')
,null,false,nvl(job_instance),false);
elsif to_number(to_char(sysdate,'hh24mi')) between 1800 and 2359 then
dbms_job.submit(job_no,job_name,
next_day(sysdate + 15/1440,'TUESDAY')
,null,false,nvl(job_instance),false);
elsif to_number(to_char(sysdate,'hh24mi')) between 1800 and 2359 then
dbms_job.submit(job_no,job_name,
next_day(sysdate + 15/1440,'WEDNSDAY')
,null,false,nvl(job_instance),false);
elsif to_number(to_char(sysdate,'hh24mi')) between 1800 and 2359 then
dbms_job.submit(job_no,job_name,
next_day(sysdate + 15/1440,'THURSDAY')
,null,false,nvl(job_instance),false);
elsif to_number(to_char(sysdate,'hh24mi')) between 1800 and 2359 then
dbms_job.submit(job_no,job_name,
next_day(sysdate + 15/1440,'FRIDAY')
,null,false,nvl(job_instance),false);
this code does not work.How do I schedule it?



and Tom said...

ops$tkyte@ORA817DEV.US.ORACLE.COM> variable n number;
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view next_date
2 as
3 select case
4 when (to_char( sysdate, 'hh24' ) between 6 and 17
5 and to_char(sysdate,'dy') NOT IN ('sat','sun') )
6 then trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/900)+1)*15/24/60
7 when (to_char( sysdate, 'dy' ) not in ('fri','sat','sun') )
8 then trunc(sysdate)+1+6/24
9 else next_day( trunc(sysdate), 'Mon' )+6/24
10 end interval_date
11 from dual
12 /

View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 dbms_job.submit( :n,
3 'null;',
4 sysdate,
5 '(select * from next_date)'
6 );
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_job.run( :n );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char( sysdate, 'hh24:mi:ss' ), to_char(next_date,'dd-mon-yyyy hh24:mi:ss' )
2 from user_jobs
3 where job = :n
4 /

TO_CHAR( TO_CHAR(NEXT_DATE,'D
-------- --------------------
12:52:15 08-aug-2002 13:00:00

ops$tkyte@ORA817DEV.US.ORACLE.COM>


Will do that for you -- the view will return the proper next date any time you select from it.

Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

extremely useful approach

A reader, August 08, 2002 - 1:12 pm UTC


A reader, August 08, 2002 - 2:08 pm UTC

Extremely helpful!

Is there any advantage/disadvantage in creating next_date as a function instead of a view?

Tom Kyte
August 08, 2002 - 2:51 pm UTC

In 817, CASE is not available in PLSQL unless you use dynamic sql.  So, you would have to code:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function next_date return date
  2  as
  3      l_date date;
  4  begin
  5      execute immediate
  6      'select case
  7              when (to_char( sysdate, ''hh24'' ) between 6 and 17
  8                        and to_char(sysdate,''dy'') NOT IN (''sat'',''sun'') )
  9                   then trunc(sysdate)+(trunc(to_char(sysdate,''sssss'')/900)+1)*15/24/60
 10              when (to_char( sysdate, ''dy'' ) not in (''fri'',''sat'',''sun'') )
 11                   then trunc(sysdate)+1+6/24
 12              else next_day( trunc(sysdate), ''Mon'' )+6/24
 13              end interval_date
 14         from dual' INTO l_date;
 15  
 16      return l_date;
 17  end;
 18  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ),
  2         to_char(next_date(),'dd-mon-yyyy hh24:mi:ss' )
  3    from dual
  4  /

TO_CHAR(SYSDATE,'DD- TO_CHAR(NEXT_DATE(),
-------------------- --------------------
08-aug-2002 14:50:29 08-aug-2002 15:00:00

ops$tkyte@ORA817DEV.US.ORACLE.COM>

which works, just not as "pretty" 

Hmmm - can't schedule a snapshot this way

andrew, August 08, 2002 - 6:13 pm UTC

Tom

I tried to use this method to schedule a snapshot, but it won't allow custom functions or subqueries.
ORA-04044: procedure, function, package, or type is not allowed here
ORA-22818: subquery expressions not allowed here
If I try the whole case statement, I get "ORA-01401: inserted value too large for column" (user_jobs.interval is varchar2(200)).

I guess the only remaining way is to create the snapshot with an arbitrary INTERVAL and then edit the corresponding DBMS_JOB entry to use the select or the function?

Tom Kyte
August 09, 2002 - 8:37 am UTC

Yes that will work <b>HOWEVER, beware exp/imp and other possible side effects</b>.  Consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot foo
  2  build immediate
  3  refresh complete
  4  next next_date
  5  as
  6  select * from scott.emp
  7  /
next next_date
     *
ERROR at line 4:
ORA-00984: column not allowed here

<b>shows it really wants a DATE in that sql statement...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot foo
  2  build immediate
  3  refresh complete
  4  next sysdate+10000
  5  as
  6  select * from scott.emp
  7  /

Materialized view created.

<b>so use a bogus date and then</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> column job new_val J
ops$tkyte@ORA817DEV.US.ORACLE.COM> select job from user_jobs;

       JOB
----------
        97

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_job.interval( &J, 'next_date' );

PL/SQL procedure successfully completed.


<b>change the job.  But, if you export that and imp show=y, you'll find:</b>

...
 "ALTER FUNCTION "NEXT_DATE" COMPILE TIMESTAMP '2002-08-09:08:22:11'"
 "CREATE SNAPSHOT "FOO" USING ("FOO", (3, 'ORA817DEV.US.ORACLE.COM', 1, 0, "S"
 "COTT", "EMP", '2002-08-09:08:22:11', 0, 41340, '1950-01-01:12:00:00', '', 0"
 ", (1, "EMPNO", "EMPNO", 0, 321, 0)), 2097249, 4, ('1950-01-01:12:00:00', 31"
 "0, 0, 0)) REFRESH COMPLETE WITH PRIMARY KEY AS"
 "SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."
 ""MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COM"
 "M","EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP""
 "BEGIN   dbms_job.isubmit(job=>97,what=>'dbms_refresh.refresh(''"OPS$TKYTE"."
 ""FOO"'');',next_date=>to_date('2029-12-25:08:22:11','YYYY-MM-DD:HH24:MI:SS'"<b>
 "),interval=>'sysdate+10000 '</b>,no_parse=>true); END;"
 "BEGIN   dbms_refresh.make('"OPS$TKYTE"."FOO"',list=>null,next_date=>null,in"
 "terval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>97,rollback_seg=>NULL,p"
 "ush_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,parall"
 "elism => 0,heap_size => 0); END;"
 "BEGIN   dbms_refresh.add(name=>'"OPS$TKYTE"."FOO"',list=>'"OPS$TKYTE"."FOO""
 "',siteid=>0,export_db=>'ORA817DEV.US.ORACLE.COM'); END;"
 "ALTER TABLE "FOO" ENABLE CONSTRAINT "EMP_PK""
Import terminated successfully without warnings.

It would be back to "normal".  You need to remember to fix the date again.

It might be better to

o create the snapshot WITHOUT an interval
o schedule the

begin
   dbms_job.submit( job => :n,
                    what => 'dbms_refresh.refresh( ''"OPS$TKYTE"."FOO"'' );',
                    next_date => next_date(),
                    interval => 'next_date' );
end;
/

call yourself.

 

Unexpected side effect

andrew, August 09, 2002 - 1:21 pm UTC

Thanks for pointing the exp/imp issue out - I wouldn't have thought of that. I'm glad I asked.

dbms_job

Lulekwa, August 19, 2002 - 3:53 am UTC

It is extremely useful!
It worked.

Reader

A reader, September 26, 2002 - 2:05 am UTC

Tom,
Why do I get the ORA-1422

Thanks

SQL> select * from next_date;

INTERVAL_
---------
27-SEP-02

SQL> begin
  2         dbms_job.submit( :n,
  3                          'null;',
  4                          sysdate,
  5                         '(select * from next_date)'
  6                        );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 586
ORA-06512: at line 2

 

Tom Kyte
September 26, 2002 - 7:49 am UTC

what does select count(*) from next_date return?

You don't have 2 rows in DUAL do you?


You also neglect to mention a version.....

Reader

A reader, September 26, 2002 - 9:43 pm UTC

Tom,

You are absolutely right. The count(*) from dual was > 1.
Although count(*) for x$dual remained at 1. 

How do x$dual and dual inter-related

What causes the count(*) for dual to multiply. I have not
run sql.bsq more than, only one time during DB creation

what is the preferred method of fixing the count

Thanks

 
Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\josephn>cd c:\

C:\>sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 26 08:44:38 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

  COUNT(*)
----------
         6

SQL> select * from x$dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
00F974C8          0          1 X

SQL> select count(*) from x$dual;

  COUNT(*)
----------
         1

SQL> truncate table sys.dual;

Table truncated.

SQL> insert into sys.dual values ('X');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from dual;

  COUNT(*)
----------
         1 

Tom Kyte
September 27, 2002 - 7:34 am UTC

dual and x$dual are not related.

x$dual IS dual before the database is opened.
dual is dual AFTER the database is opened.


what causes dual to have more then one row? people do. someone inserted a row in there. to fix:

delete from dual;
insert into dual values ('X');
commit;


and never never touch a SYS table directly again.

A reader, October 01, 2002 - 6:00 pm UTC

Hi tom,

I have two questions related to the original post:

1-I don’t understand very well the numbers that you used in your original answer:
<quote>
trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/900)+1)*15/24/60
<quote>

I understand that 900=15*60, but why did you use that way to resolve that issue?, what does exactly mean every term in your calculation ( [ trunc(sysdate) ] + [ [(trunc(to_char(sysdate,'sssss')/900)+1)] *15/24/60 ] )?

2-I need to schedule a procedure that should ran every hour starting at 5:40 am , so the run hours should be 5:40, 6:40,7:40 and so on. I tried to modify your view replacing 900 by 3600 and 15 by 60, but it didn’t work and as I don’t understand your calculation is difficult to me to figure out what I am doing wrong. Would you mind helping me with this?

Thanks in advance.


Tom Kyte
October 02, 2002 - 10:02 am UTC

for 5:40 on -- tis even easier. we just need to trunc sysdate to the hour, add an hour and 40 minutes:

trunc(sysdate,'hh') + 1/24 + 40*(1/24/60)



At 5:40, trunc(sysdate,'hh') will return 5am. adding 1/24 (1/24th of a day, 1 hour) will give 6am. Adding 40 * (1/24/60) will add 40 minutes... (adding N * (1/24/60/60) will add N seconds)


It is all math -- add 1 to sysdate and you get tomorrow at the same time. Add 1/2 -- you just added 1/2 day (12 hours) and so on.

So, in my:

trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/900)+1)*15/24/60

trunc(sysdate) is MIDNIGHT today (wipe out the time).

Then rest of it was figuring out how many 15 minute intervals had happened today alert (the /900 stuff) and adding 1 more 15 minute interval to it -- so we get nice 1:15, 1:30, 1:45, 2:00, 2:15 and so on.


If you have my book "Expert one on one Oracle" -- it goes into this in more detail (not this particular example, but the whole "setting the intervals for jobs" question.


DBMS_JOB KEEPS RESUBMITTING ITSELF

P, October 31, 2002 - 6:25 pm UTC

I have submitted a job via dbms_jobs. I want the job to run ONLY ONCE despite the fact that it may fail.
Currently, dbms_jobs resubmits the job severaltimes.

I would like dbms_jobs to stop after the FIRST failure.

How to do this?

Also If I remove some job using
DBMS_JOB.REMOVE(1119);
while it is in queue and not running it doesnot go away from DBA_JOBS or doesnt remove it.

please explain....


Tom Kyte
October 31, 2002 - 9:33 pm UTC

submit the job like this:


dbms_job.submit( l_job,
'begin
procedure;
exceptions
when others then
null; /* blindly ignore the error */
end;' );




dbms_job.remove doesnt work

P, October 31, 2002 - 9:45 pm UTC

hey tom

why dbms_job.remove does not remove it from queue?

If I remove some job using
DBMS_JOB.REMOVE(1119);
while it is in queue and not running it doesnot go away from DBA_JOBS or doesnt
remove it.



Tom Kyte
October 31, 2002 - 9:48 pm UTC

give me an example like this:

ops$tkyte@ORA920.US.ORACLE.COM> variable n number
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.submit( :n, 'Null;' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM> print n

         N
----------
         4

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         4 Null;

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.remove( :n );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select job, what from user_jobs;

no rows selected


that shows it does not work on your system.  works on all of mine. 

why resubmit?

P, October 31, 2002 - 11:25 pm UTC

hi tom,
EXCEPTION
WHEN OTHERS THEN
NULL;

IS FINE

BUT COULD YOU PLEASE EXPLAIN WHY THIS IS HAPPENING?

WHY IT RESUBMITTS THE JOB AGAIN IF ERROR?


Tom Kyte
November 01, 2002 - 6:34 am UTC

BECAUSE THAT IS THE DEFINED BEHAVIOR OF THE JOB QUEUES.

It is the way they are designed to work -- run the job, if it fails -- assume it is a temporary error (network down, whatever). Run the job again later. try 16 times before marking the job as "broken".

It is the way they were built to operate.

All you need to do is make it so the queued job "succeeds", thats what this does.

I don't see the purpose however in running a job, letting it fail and just ignoring that. Why bother even running the job?

Sagi, November 01, 2002 - 8:22 am UTC

Hi Tom,

Regarding DUAL table you said

"x$dual IS dual before the database is opened. dual is dual AFTER the database is opened."

Q1) Please see the commands executed below.

SVRMGR> connect internal
Connected.
SVRMGR> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 47249436 byte
Fixed Size 75804 byte
Variable Size 30318592 byte
Database Buffers 16777216 byte
Redo Buffers 77824 byte
Database mounted.
SVRMGR>
SVRMGR>
SVRMGR> select * from dual ;
ADDR INDX INST_ID D
-------- ---------- ---------- -
00F974C8 0 1 X
1 row selected.

SVRMGR> select * from x$dual ;
ADDR INDX INST_ID D
-------- ---------- ---------- -
00F974C8 0 1 X
1 row selected.

SVRMGR> alter database open ;
Statement processed.
SVRMGR> select * from dual ;
D
-
X
1 row selected.
SVRMGR>

When the DB is mounted and I referr to either DUAL or X$DUAL both gives me same output. Here my question is when i say DUAL how does it internally refer or resolve to X$DUAL.

q2) What is the significance of having X$DUAL and DUAL before and after DB is open. Any specific usage ...

q3) When the DB is "opened" the SYS.DUAL is with a single column, where as in "mount" it was 4 columns.

Thanx in advance and waiting for your valuable reply.

Regards,
Sagi



Tom Kyte
November 01, 2002 - 3:34 pm UTC

q1) just take the word that "x$dual is dual before the database is opened". thats all we (you and me both) need to know. dual is special, dual is magic, dual is handled differently then other tables under the covers.

q2) things that need dual (eg: rman) and that would fail without a dual like table....

q3) statement of fact -- yes, that is true.

here is why run the jobs...

P, November 01, 2002 - 12:18 pm UTC

Hi Tom,
You said---------------
I don't see the purpose however in running a job, letting it fail and just
ignoring that. Why bother even running the job?
-----------
Because these jobs create reports and store them in DB as a clob and we donot care if they fail because this is done after 18 months so if failure then correct error and startover again.


Tom Kyte
November 01, 2002 - 4:09 pm UTC

but how will you know they failed ;)

You can set the job to broken like this instead (now that I know what you WANT). We'll make the job run once -- if it fails, it will break itself right off, but stay IN the queue in a broken status with a run date way in the future.  so at least you know it "didn't run".

Here is an example (first we'll see how to get the text used by oracle to execute your job so you can see how to set broken and next_date!)



ops$tkyte@ORA920.US.ORACLE.COM> variable n number
ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.submit( :n, ' /* tkyte */ null;' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.run(:n);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> select sql_text from v$sql where sql_text like '%tkyte%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN  /* tkyte */ null; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

select sql_text from v$sql where sql_text like '%tkyte%'

<b>there it is, we can just set the variables BROKEN and NEXT_DATE to whatever to tell oracle the job is broken....</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure big_time_failure
  2  as
  3      x int;
  4  begin
  5      x := 1/0;
  6  end;
  7  /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> exec big_time_failure;
BEGIN big_time_failure; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.BIG_TIME_FAILURE", line 5
ORA-06512: at line 1

<b>so, that'll never work...</b>

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> begin
  2      dbms_job.submit( :n,
  3                      'begin  /* tkyte */
  4                          big_time_failure;
  5                       EXCEPTION
  6                          when others then
  7                             broken := TRUE;
  8                             next_date := to_date(''01019999'', ''ddmmyyyy'');
  9                       end;'
 10                     );
 11      commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.run( :n );

PL/SQL procedure successfully completed.

<b>"appears" to have run but....</b>

ops$tkyte@ORA920.US.ORACLE.COM> select sql_text from v$sql where sql_text like '%tkyte%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN begin  /* tkyte */
       big_time_failure;                      EXCEPTION                         when others then                           broken
:= TRUE;                            next_date := to_date('01019999', 'ddmmyyyy');                      end; :mydate := next_date; I
F broken THEN :b := 1; ELSE :b := 0; END IF; END;

select sql_text from v$sql where sql_text like '%tkyte%'

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select job, what, broken, failures from user_jobs where job = :n;

       JOB WHAT                           B   FAILURES
---------- ------------------------------ - ----------
       223 begin  /* tkyte */             Y          0
           big_time_failure;
           EXCEPTION
           when others then
           broken := TRUE;
           next_date :=
           to_date('01019999',
           'ddmmyyyy');
           end;

<b>we can see it is now just a "broken" job...</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_job.remove( :n );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> 

what if

miki, March 18, 2003 - 9:04 am UTC

Hi Tom,

Could you give me an example about dbms_job.submit if i want to call set of procedures in every last day of months.

thanks in advance,

miki

Tom Kyte
March 18, 2003 - 10:18 am UTC

the interval would be

add_months(last_day(trunc(sysdate)),1)




Job Execution

vicky, March 19, 2003 - 4:59 am UTC

hello sir ..

Sir i scheduled my job for execution after one hour...
when my database goes shutdown for greater than 1 hour
( interval time ) after that my does not execute ...
because system time is Greater then jobs Next_date ...

how i can schedule my job for executing permanently



Tom Kyte
March 19, 2003 - 7:05 am UTC

that is not correct. the job will execute shortly after startup. The job queues look for all jobs that SHOULD HAVE BEEN run and runs them.

if the job is NOT running after a restart -- perhaps your job queue processes in your pfile is set to zero??? and someone needs to do an alter system and update the pfile to fix that?

Excellent

Ashish Somani, May 12, 2003 - 11:30 am UTC

Nice tips given which r really helpful and helped me a lot in setting the jobs and find out many wayouts.

Would like say I thing,

TOM your r great would like to meet you once in my life.

Don't automaticlly run job after instance startup

Minnie, May 30, 2003 - 4:09 pm UTC

Hi Tom,
Thank you for your excellent job.
I have a question about the automatic job running after startup.

as you said

......
that is not correct. the job will execute shortly after startup. The job
queues look for all jobs that SHOULD HAVE BEEN run and runs them.
.........

There is a job which cleans up a table every 4am. This table is critical to the application during the daytime. I don't want this job automaticly runs when the database startups after 8am if it's crashed before 4am. How can I achive that?

Thanks again,

Tom Kyte
May 31, 2003 - 11:37 am UTC


create procedure that_4am_job
as
begin
if ( to_char(sysdate,'hh24') not in ( <list of acceptable hours to run ) )
then
maybe send an email to someone to let them know.
return;
end if;

rest of code
end;


How to execute a job after 1 day from sysdate

Robert, January 13, 2004 - 1:51 pm UTC

I am new to job scheduling and almost Oracle also. How to schedule a job to run after 24 hours from current sysdate including hours,minutes and seconds.

Tom Kyte
January 13, 2004 - 5:54 pm UTC

exec dbms_job.submit( :job, 'thing;', sysdate+1 );



how to remove jobs

A reader, January 18, 2004 - 2:57 pm UTC

Hi Tom:
I added a job, now i wanted to remove the job.
i did this
DBMS_JOB.REMOVE(2);

but it didnot help. the job is still running. any idea what is happening?

Tom Kyte
January 18, 2004 - 3:25 pm UTC

you cannot remove a running job.

what you can do is

a) alter system set job_queue_processes=0;
b) alter system kill session 'x,y'; where x,y is the sid/serial# of the running job
c) dbms_job.remove, commit;
d) alter system set job_queue_processes=N; where N = number of job queues you want

that always works for me.

No need to set job_queue_process=0 to kill a running job

Jurij Modic, January 18, 2004 - 4:41 pm UTC

Tom, just want to throw my 0.02$ on the subject of killing a running job. Setting job_queue_process to 0 just to kill the running job could be a bit of overkill - we wouldn't want to disturb the execution of other jobs that might be due to start just in next few seconds.

More elegant way is:
a) dbms_job.broken(:runnig_job,'TRUE'); commit;
b) alter system kill session 'x,y';
c) dbms_job.remove, commit;


Tom Kyte
January 19, 2004 - 8:57 am UTC

well. given that the whole of a,b,c,d should take no more than 15 seconds.... but yet, that is a viable path as well (your a,b,c).

A reader, January 19, 2004 - 9:12 am UTC

Tom,

If 'ss' are for seconds but what are these five 'sssss'

scott@TEST> select to_char(sysdate,'sssss'),sysdate from dual;

TO_CH SYSDATE
----- -------------------
28939 01/19/2004 08:02:19


Thanks.
Greg.


Tom Kyte
January 19, 2004 - 9:55 am UTC

seconds since midnight.

dbms_jobs

S.Darshan, February 12, 2004 - 11:29 pm UTC

Every day my job is running at particular time at night. It will run every day one time. But last night it did n't run. No failures at all. No Error message at alert log file. How can we find the reason? Can you pls give me some solutions?

With best regards
S.Darshan

Tom Kyte
February 13, 2004 - 9:47 am UTC

so, what sort of logging do you have in your job.

what is running your job. (could be anything, dbms_job, oem, somthing else, cron, at, whatever)

dbms_job

S.Darshan, February 14, 2004 - 12:08 am UTC

Thank you very much for your reply.
It is a oracle dbms_job. 
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
System name:    SunOS
Release:    5.8
Version:    Generic_108528-26
Machine:    sun4u

The follwoing are related informaiton:
SQL> select name,value from v$parameter where name  like 'job%';

NAME                                     VALUE
---------------------------------------- -------------------------
job_queue_processes                      4
job_queue_interval                       60



SQL> select job,to_char(last_date,'DD-MON-YY HH24:MI') Last_date,
  2         to_char(next_date,'DD-MON-YY HH24:MI') Next_date,
  3         broken,interval,what,failures 
  4  from user_jobs;

      JOB LAST_DATE       NEXT_DATE       B INTERVAL                  WHAT                      
--------- --------------- --------------- - ------------------------- ---------------
     1882 14-FEB-04 04:45 15-FEB-04 00:40 N trunc(sysdate)+1.027778   pkgjob.mid; 

         Based on the Intervel it will run every day midnight 12.40. But last night It did n't run.  I ran dbms_job.run manually to exec the  above job. So that last time 04.45. 

        What coluld be cause?  Can you help me?

With best regards
S.Darshan 


 

Tom Kyte
February 14, 2004 - 4:59 pm UTC

anything in the alert log?
any jobs running?


too late to look now but -- did you happen to look at the failures OR broken columns before running it (which would reset them, so too late to look see now)

a job marked as broken after 16 tries

A reader, March 02, 2004 - 10:44 pm UTC

hi tom,

I have a dbms job to refresh a materilized view between 2 unix boxs. In order to cover the down time ( about 2 hours for manintenance ) of the machine where the base table resides, what value i should set for JOB_QUEUE_INTERVAL if the interval for executing the job is specified as 1 minute?

1) if I set JOB_QUEUE_INTERVAL to 59 sec, since JOB_QUEUE_INTERVAL < job_exec_interval I will get exponential backoff for every 1,2,4,8,16,32,64,128 min,
which will cover 2 hr down time.

2) if JOB_QUEUE_INTERVAL = 600 (10 minutes),
the job will try once every 10 minutes, for up to 16 tries, so 160 min covers 2 hr down time.

which way is better?

and where can I get specific time of 16 attemps a job tried?

Thanks

steve

Tom Kyte
March 03, 2004 - 9:17 am UTC

why not set the next date to 2 hours from now and leave the interval alone? or break the job and unbreak it after the downtime window is done?

purge jobs ??

A reader, April 29, 2004 - 2:26 pm UTC

Hello Tom, thanks for your great site !!!
I have a job which takes a snapshot every 15 minutes
==================================================
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
commit;
end;
/
============================================

This script works fine. We have another crontab job which runs the statsreport and generates the report against the snapshots created in earlier script.

Now I want to purge the snaps which are greater than 2 months old. How can I do this?



Tom Kyte
April 29, 2004 - 2:52 pm UTC

sppurge


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6722856149512 <code>

(but beware, the technique might vary slightly from release to release)

thanks Tom

A reader, April 30, 2004 - 10:26 am UTC


A reader, June 09, 2004 - 3:19 pm UTC

a basic question on dbms_job.submit on Oracle 9.2.0.1.

The job_queue_interval parameter is obsolete in 9i. If I say
declare
fd numeric;
begin
dbms_job.SUBMIT(fd,'doesnothing;',SYSDATE);
end;

I see an entry in user_jobs and it is there from almost an hour.

When does this job execute?

Is there a way to say our own definied interval say 60 seconds.

Thanks

Tom Kyte
June 09, 2004 - 4:19 pm UTC

you are probably missing the "commit" -- it'll stay there forever until you commit it.

You can see it in your session -- but the job queues cannot :)



Some clerification on dbms_jobs.

SK, August 18, 2004 - 9:21 pm UTC

Tom,

If I set a replication job to be run every 5 minutes. Then what happenes if the previous job does not finish in 5 minutes. Does oracle starts another job, even if old job is still not complete. How is the Next column handled in this situation.

Thanks

Tom Kyte
August 18, 2004 - 11:45 pm UTC

the job will run to completion and then set it's "next time" which was computed at the beginning of the job - -meaning it'll run ASAP (since its next run time already happened)

in short

o it won't run another run until the running one is done
o the "next" date is evaluated when the job starts
o so if the job started at 12:00, it would be scheduled to run at 12:05 -- but if the job that started at 12:00 didn't finish until 12:10 -- it would just run ASAP after the 12:10 completion.

Further clarification on Job

SK, August 19, 2004 - 9:11 am UTC

Tom,

Let us say thet job is scheduled to run every 5 minute. The job started at 12:00, its next schedled execution time is 12:05, but prev job finished at 12:07. So nxt job will start at 12:07 (as soon as the prev job finished). So now what will be the next execution time for the job. Will it be 12:12 or 12:10.

I see a potential of skipping some jobs due to earlier jobs finishing late. Am I correct?

Thanks

Tom Kyte
August 19, 2004 - 10:03 am UTC

the next execution time of the job that runs at 12:07 will be computed using YOUR function -- so you tell me, will YOUR function run at 12:07 return

a) 12:10
b) 12:12

??? only you can tell -- cause only you know the function used. If you did your function so that "jobs don't slip" -- it would be 12:10. If you just said "schedule it to run 5 minutes after right now" it would be 12:12.

YOU control it.

I don't see how you can "skip some jobs" as there is "just the SINGLE job" we are talking about here and this single job will run at most one copies of itself and this job controls when it next runs.

Good Solution

atchayya paruchuri, December 05, 2005 - 11:34 am UTC

That was a good approach

Job should run every second

A reader, October 25, 2006 - 9:54 pm UTC

Job should run every second, how should I set the interval?

Tom Kyte
October 25, 2006 - 9:59 pm UTC

won't happen, not reasonable, rethink your needs here


question for you: WHY, what is it doing.

Job/sec

Billy, October 26, 2006 - 2:07 am UTC

>> Job should run every second, how should I set the
>> interval?
>
> question for you: WHY, what is it doing.

Well, that should be easy to determine Tom.

Obviously it must do what it does in under a second. So it is not doing much.

Which means it is not doing anything much on a continuous basis. Except to anoy the hell ouf the DBA, wanting him to take his lead pipe and....

Yeah.. there is no problem that cannot be solved with the dedicated, careful and focussed application of mindless bloody violence.. using lead pipes of course.

You want to borrow me lead pipe Tom?

;-)

Tom Kyte
October 26, 2006 - 11:50 am UTC

nah, no need for a lead pipe, usually just asking "why" is sufficient :)

Rumburak

Rumburak, October 26, 2006 - 4:04 am UTC

LOOP
DBMS_LOCK.SLEEP(1);
its_a_joke_isnt_it_job();
END LOOP;
/

Good Luck!


DBMS_JOB INTERVALS

Krishna, July 16, 2007 - 1:54 am UTC

I want to set interval to a particular time in a day for a job. I have set a job and interval as sysdate + 1 but interval set after the completion for the current running job. for example job is running at 6.00 pm it completes by 8.00 pm. the next time job runs is 8.00pm the next day instead of 6.00 pm the next day. Can you please give a solution.
Declare job  Integer;
Begin
  sys.dbms_job.submit(job => job,
                      what => 'sin_blkinvupload_scheduler;',
                         interval => 'SYSDATE+1');
  commit;
end;

Tom Kyte
July 17, 2007 - 11:04 am UTC

use non-relative dates.

trunc(sysdate)+1+18/24


take today at midnight, add one day (tomorrow) and then 18 hours (6pm)

no matter when you execute that, it'll always be 6pm tomorrow as the result

job schedule

prasanna, August 28, 2007 - 8:16 pm UTC

Hi Tom,
I need to schedule a process (stored procedure execution that send out status email only when a table satisfies a business rule) using dbms_job.
Since I built this send email as a generic procedure, I wrote a wrapper procedure over it that does the above business rule checks and if it satisfies the rules, the invokes the send email procedure.
Else, does nothing.
So far,so good...

Now, There is one more rule that I need to satisfy:
1) I need to start scheduling this wrapper from the first day of every month starting at 00:00 hours and at 1 hour intervals.
This also is fine as I would then use dbms_job by setting:
next_date as trunc(sysdate)
interval as 'trunc(sysdate)+ 1/24'

So far , so good...

Here comes the tricky part...
Once the business rule is satisfied say on the 2nd day nth hour or whenever, the email SP inside the wrapper would get triggered and a mail would be sent...
We want to stop this job from executing again subsequently as the business rules have already been satisfied for this month and we want this same job to schedule itself starting from the first day of the next month...
And this cycle continues...
I am having some confusion here.
We are on 9.2.0.8.

Would be great if you can throw some ideas...

Thanks!
Tom Kyte
September 04, 2007 - 3:44 pm UTC

stop it, just stop it.

ask once, period - in one place.

dbms_jobs on RAC

A reader, October 10, 2007 - 5:11 pm UTC

How does dbms_job choose instance in RAC database? Does it use tnsnames, services, etc?

If I want my dbms_job to execute on one particular node always, how do I do that?

Thanks,

dbms_job on RAC

Greg, July 08, 2008 - 10:15 am UTC

Check the Oracle docs http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_job.htm#sthref4319 for an example, but basically, use the INSTANCE parameter to dbms_job.submit:
PROCEDURE SUBMIT
 Argument    Type                In/Out Default?
 ----------- ----------------- ------ --------
 JOB       BINARY_INTEGER          OUT
 WHAT      VARCHAR2                IN
 NEXT_DATE DATE                    IN     DEFAULT
 INTERVAL  VARCHAR2                IN     DEFAULT
 NO_PARSE  BOOLEAN                 IN     DEFAULT
 INSTANCE  BINARY_INTEGER          IN     DEFAULT
 FORCE     BOOLEAN                 IN     DEFAULT

Or, as the manual recommends, change to dbms_scheduler

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library