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?
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?
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
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
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 dont 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 didnt work and as I dont 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.
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....
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.
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?
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
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.
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
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
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,
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.
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?
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;
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.
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
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
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
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?
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
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
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
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?
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?
;-)
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;
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!
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