Hi, Tom,

I would really appreciate your help here...
We have a job to be run and we submit it
using dbms_job.submit(:job, job_block, SYSDATE, 'SYSDATE + 1/24/60/60');
But this job doesn't start neither right now nor at any point of time later.
We have job_queue_processes = 10 and job_queue_interval = 5.
We checked it out: all SNPX processes are started and running (but are they running properly?)

What makes us think we are stupid is that we have another server with only one difference - default codepage. and the job starts with no problems on the second server.

We have oracle 8.1.7 with patch 3 installed on both servers

...?

commit;


I'll bet you are forgetting the commit;

If you go into sqlplus and issue:

SQL> exec dbms_job.submit( :n, 'null;' );

that job will never run until you commit it. 

I wish it would have been that easy...

Well, we DO issue commit; but i repeat,
the job does NOT start.
And what is more confusing is that i
can run the job explicitly using dbms_job.run.
And it's executed, but only once.
further executions that have to occur almost
every second do not occur.

Alert and smon trace files are clean.

We have no clue what to do...

SQL> variable n number;
SQL>   exec dbms_job.submit( :n, 'null;' );

PL/SQL procedure successfully completed.

SQL>   commit;

Commit complete.

SQL> show parameter job

NAME                                 TYPE           VALUE
------------------------------------ -------------- ----------------------------
--
job_queue_interval                   integer        5
job_queue_processes                  integer        10

SQL> select * from user_jobs;

  JOB LOG_USER    PRIV_USER   SCHEMA_USER   LAST_DATE LAST_SEC    THIS_DATE THIS_SEC
----- ----------- ----------- ------------- --------- ----------- --------- -----------
  140 QIM         QIM         QIM

NEXT_DATE NEXT_SEC    TOTAL_TIME BR INTERVAL     FAILURES WHAT
--------- ----------- ---------- -- ---------- ---------- -------
18-MAR-02 10:19:22             0 N  null                  null;

NLS_ENV
-----------------------------------------------------------------------------------------
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='CHINA' NLS_CURRENCY='RMB' NLS_ISO_CURRENCY='CHINA'


-----------------------------------------------------------------------------------------------------------
 NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'

MISC_ENV              INSTANCE
------------------- ----------
0102000200000000             0


SQL> exec dbms_lock.sleep( 30 );

PL/SQL procedure successfully completed.

SQL> select * from user_jobs;


  JOB LOG_USER    PRIV_USER   SCHEMA_USER   LAST_DATE LAST_SEC    THIS_DATE THIS_SEC
----- ----------- ----------- ------------- --------- ----------- --------- -----------
  140 QIM         QIM         QIM

NEXT_DATE NEXT_SEC    TOTAL_TIME BR INTERVAL     FAILURES WHAT
--------- ----------- ---------- -- ---------- ---------- -------
18-MAR-02 10:19:22             0 N  null                  null;

NLS_ENV
------------------------------------------------------------------------------------------
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='CHINA' NLS_CURRENCY='RMB' NLS_ISO_CURRENCY='CHINA'


----------------------------------------------------------------------------------------------------------
NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'

MISC_ENV              INSTANCE
------------------- ----------
0102000200000000             0


That's it. 

Hi, Tom,

unfortunately i'm NT, well in fact win2000
here is our OS info (tell me if the info is not enough):
System:
  MS Win 2000 5.00.2195 SP 2
Computer:
  x86 Family 6 Model 8 Stepping 3 GenuineIntel
  AT/AT 1Gb RAM
some db info:
  
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE    8.1.7.2.1       Production
TNS for 32-bit Windows: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Production 

what else?

yes, i did some "alter system" stuff:

SQL> alter system set job_queue_processes = 11
  2  /

System altered.

  >> after this ALRT.LOG showed this:
   ALTER SYSTEM SET job_queue_processes=11;
   Mon Mar 18 15:30:46 2002
   Restarting dead background process SNPA
   SNPA started with pid=25
  >> and the number of oracle.exe's threads in Windows Task Manager increased by 1 (=36)


SQL> alter system set job_queue_processes = 12
  2  /

System altered.

  >> after this ALRT.LOG showed this:
   ALTER SYSTEM SET job_queue_processes=12;
   Mon Mar 18 15:30:58 2002
   Restarting dead background process SNPB
   SNPB started with pid=29
  >> and the number of oracle.exe's threads in Windows Task Manager increased by 1 (=37)

 

No, 

I've done one more query for you, Tom,

SQL> SELECT RAWTOHEX(paddr) paddr_hex, name FROM v$bgprocess
  2      WHERE RAWTOHEX(paddr) <> HEXTORAW(0)
  3      AND name LIKE 'SNP%';

PADDR_HEX        NAME
---------------- ----------
067BC31C         SNP0
067BC644         SNP1
067BC96C         SNP2
067BCC94         SNP3
067BCFBC         SNP4
067BD2E4         SNP5
067BD60C         SNP6
067BD934         SNP7
067BDC5C         SNP8
067BDF84         SNP9
067BF8C4         SNPA
067C0564         SNPB

12 rows selected.


 

Hi, Tom,

quering dba_jobs_running produced zero rows in result.

Well, yes we can restart the server, it's the
development instance, but we restarted it twice
during previous week with no changes to jobs' status.

We'll think about forwarding this issue to support
when we have an emergency.

Thank you very much for your support, Tom.

Tom,

I have two objects of DBA_JOBS_RUNNING with types 1)Synonym 2)View. When I query on DBA_JOBS_RUNNING, which object will be used to get the reuslt ?


  1* select object_name,object_type from all_OBJECTS where object_name like '%DBA_JOBS_RUNNING%'
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
DBA_JOBS_RUNNING               VIEW
DBA_JOBS_RUNNING               SYNONYM

SQL> select object_name,object_type from dba_OBJECTS where object_name like '%DBA_JOBS_RUNNING%';

OBJECT_NAME       OBJECT_TYPE                                                                                         
---------------   ------------                                                                               
DBA_JOBS_RUNNING   VIEW                                                                                                
                                                                                                    
DBA_JOBS_RUNNING   SYNONYM                                                                                             
                                                                                                    

SQL> select text from all_views where view_name='DBA_JOBS_RUNNING';

TEXT                                                                                                
--------------------------------------------------------------------------------                    
select v.SID, v.id2 JOB, j.FAILURES,                                                                
    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,                                
    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,                                
    j.field1 INSTANCE                                                                               
  from sys.job$ j, v$lock v                                                                         
  where v.type = 'JQ' and j.job (+)= v.id2                                                          
                                                                                                    

SQL>  select * from all_synonyms where synonym_NAME='DBA_JOBS_RUNNING'
  2  ;

OWNER   SYNONYM_NAME     TABLE_OWNER    TABLE_NAME  DB_LINK                                                                                             
------- --------------   ------------   ----------- ---------
PUBLIC  DBA_JOBS_RUNNING  SYS DBA_JOBS_RUNNING


 

Tom,
I recently changed times on my snapshot refreshes.
There were couple that showed that they ran in dba_jobs and dba_snapshots.
but in dba_snapshots the "START_WITH" column was null.
Although...dba_jobs had next_date, next_sec column populated with correct values...
There is no error in the alert log about these jobs..
Can you help?

Thanks

snapshot ran..bt dba_snapshot.start_with column was null

tom,

I have a requirement, where I need to run a packaged procedure when a set of tables are populated. ( Tables A, B, c). Ex:Data (from flat files ) would be loaded into A, B, C every morning at 12:30 AM .After each table is loaded the process updates a log table (D) and sets up a flag saying table is loaded. I need to check that all the 3 tables are loaded and then kick off my procedure. What would be the best option?
Here is the scenario for the log table.
filename
statusflag
start_time
end_time

Tables A,B,C would be populated with filenames that start with 'XYZ'. While the data table (A,B,C) is beong loaded, the status flag would have value of "IN PROGRESS" and once finished would have value of "LOADED SUCCESSFULLY".

I can query distinct statusflag for these filenames and if I get a single name ("LOADED SUCCESSFULLY"), I need to kick off my procedure.
My question is How/where do I ste up my job ?

Thanks for your help !

I tried this view...it had totally absurd results
Last_refresh dates are old...where as dba_snapshots..and dba_jobs are all current...compile_state has error???
LAST_REFR COMPILE_STATE AFTER_FAST_REFRESH
--------- ------------- ---------
12-JUL-01 ERROR UNDEFINED
20-JUN-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
02-AUG-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
06-SEP-01 ERROR UNDEFINED
06-SEP-01 ERROR UNDEFINED
16-JUL-01 ERROR UNDEFINED
12-JUL-01 ERROR UNDEFINED
12-JUL-01 ERROR UNDEFINED
05-MAR-02 ERROR UNDEFINED
20-SEP-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
27-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
23-AUG-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
13-SEP-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
25-MAR-02 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED
10-JUL-01 ERROR UNDEFINED

I am having the same issue the user "john from Ukraine".
I installed Oracle8.1.7.3.3 on Windows2000(SP2) PentiumIV Server. I enabled JOB_QUEUE_PROCESS=2, JOB_QUEUE_INTERVAL=1.
I put commit after submitting my job. It does't start until I run dbms_job.run(1). Only after execute dbms_job.run(1), I could see LAST_SEC,LAST_DATE,FAILURES values on USER_JOBs.
These values were null before execute dbms_job.run(1).
Moreover it is not starting again.

I set JOB_QUEUE_PROCESSES=2 and JOB_QUEUE_INTERVAL=2
Even after restart my database Job has't started.
(I did't try to reboot system). But I set "ALTER SYSTEM SET JOB_QUEUE_PROCESSES=2". The output in SIDalert.log file is "Restarting dead background process SNP1". It works.
I opened tar too, but your suggestion helped me.

I have 2 jobs hitting the server at the same moment. Is there any way I can prioritize them
Thanks

Hi tom,

can you please confirm that the folling job will be
executed only on week-days and not on week-ends and
this is the good way to do that or please suggest me
what will be the better way.

I want to set up a db-job which should run every
weekday @8.0 (hh24)

my solution

declare

jobno number;

begin

DBMS_JOB.SUBMIT( jobno,'update_current_flag;',
sysdate,
select trunc(decode( to_char( d+1, 'DY') ,
'SAT', next_day(d,'MON'),
'SUN',next_day(d,'MON'),
d+1 )
)+8/24
from ( select sysdate+rownum d from dual)
);

commit;

end;
/


--Thanks,

I cannot confirm that at all -- in fact I can confirm the job COULD run on sat for sure....

how?  database goes down friday more, or you accidently stopped the job queue for some reason, the job fails for whatever reason --- 

then

database comes up on saturday after a point in time restore from backups, or you noticed the job queues were shutdown, or the reason for the job failing is suddenly fixed - the job runs on sat....

In anycase -- you can use this decode statement (obviously replacing sat/sun/mon with your local spelling):

ops$tkyte@ORA920> alter session set nls_date_format = 'Dy DD-Mon-YYYY hham';

Session altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select dt,
  2         trunc(decode(to_char(dt+1,'DY'),'SAT',next_day(dt,'MON'),'SUN',next_day(dt,'MON'),dt+1))+8/24
  3    from ( select sysdate+rownum dt from all_objects where rownum < 32 )
  4  /

DT                   TRUNC(DECODE(TO_CHAR
-------------------- --------------------
Thu 19-Dec-2002 10am Fri 20-Dec-2002 08am
Fri 20-Dec-2002 10am Mon 23-Dec-2002 08am
Sat 21-Dec-2002 10am Mon 23-Dec-2002 08am
Sun 22-Dec-2002 10am Mon 23-Dec-2002 08am
Mon 23-Dec-2002 10am Tue 24-Dec-2002 08am
Tue 24-Dec-2002 10am Wed 25-Dec-2002 08am
Wed 25-Dec-2002 10am Thu 26-Dec-2002 08am
Thu 26-Dec-2002 10am Fri 27-Dec-2002 08am
Fri 27-Dec-2002 10am Mon 30-Dec-2002 08am
Sat 28-Dec-2002 10am Mon 30-Dec-2002 08am
Sun 29-Dec-2002 10am Mon 30-Dec-2002 08am
Mon 30-Dec-2002 10am Tue 31-Dec-2002 08am
Tue 31-Dec-2002 10am Wed 01-Jan-2003 08am
Wed 01-Jan-2003 10am Thu 02-Jan-2003 08am
Thu 02-Jan-2003 10am Fri 03-Jan-2003 08am
Fri 03-Jan-2003 10am Mon 06-Jan-2003 08am
Sat 04-Jan-2003 10am Mon 06-Jan-2003 08am
Sun 05-Jan-2003 10am Mon 06-Jan-2003 08am
Mon 06-Jan-2003 10am Tue 07-Jan-2003 08am
Tue 07-Jan-2003 10am Wed 08-Jan-2003 08am
Wed 08-Jan-2003 10am Thu 09-Jan-2003 08am
Thu 09-Jan-2003 10am Fri 10-Jan-2003 08am
Fri 10-Jan-2003 10am Mon 13-Jan-2003 08am
Sat 11-Jan-2003 10am Mon 13-Jan-2003 08am
Sun 12-Jan-2003 10am Mon 13-Jan-2003 08am
Mon 13-Jan-2003 10am Tue 14-Jan-2003 08am
Tue 14-Jan-2003 10am Wed 15-Jan-2003 08am
Wed 15-Jan-2003 10am Thu 16-Jan-2003 08am
Thu 16-Jan-2003 10am Fri 17-Jan-2003 08am
Fri 17-Jan-2003 10am Mon 20-Jan-2003 08am
Sat 18-Jan-2003 10am Mon 20-Jan-2003 08am

31 rows selected.



Just replace DT with SYSDATE -- lose the select from dual

ops$tkyte@ORA920> variable n number
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2    dbms_job.submit( :n,
  3                    'null;',
  4                     sysdate,
  5                    'trunc(decode(to_char(sysdate+1,''DY''),''SAT'',' ||
  6                    'next_day(sysdate,''MON''),''SUN'',' ||
  7                    'next_day(sysdate,''MON''),sysdate+1))+8/24'
  8    );
  9  end;
 10  /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select what, next_date from user_jobs;

WHAT                           NEXT_DATE
------------------------------ --------------------
null;                          Thu 19-Dec-2002 08am

ops$tkyte@ORA920>

 

dbms_job.submit(:j,
'if to_char(sysdate,''DY'') not in (''SAT'',''SUN'') then
my_proc;
end if;',...)

or thereabouts

hth
connor

Hi Tom,
Well I have a procedure and job as follows :

create or replace procedure index_sync is
begin
ctx_ddl.sync_index('faq_question');
end;
/

declare
jobno number;
begin
dbms_job.submit( job=>jobno, what=>'index_sync;', next_date=>sysdate, interval=>'sysdate+1/24');
end;
/
commit;

This job runs every hour. Say I execute this procedure on the server at 0923 hrs so this process will run at 1023 hrs, 1123 hrs and so on.
Is it possible to start the job at next rounded hour i.e in this example say if I submit at 0923 hrs the job gets started at 1000 hrs and then run every hour.
Thanks a lot in advance.
Best Regards

i have 2 jobs coming to database from 2 procedures
Incase if they both hot the database at the same time is there a way where in I can set the priority for them?

so tom,

as the output of the query..

/**************************************************/
ops$tkyte@ORA920> select dt,
2
trunc(decode(to_char(dt+1,'DY'),'SAT',next_day(dt,'MON'),'SUN',next_day(dt,'MON')
,dt+1))+8/24
3 from ( select sysdate+rownum dt from all_objects where rownum < 32 )
4 /

DT TRUNC(DECODE(TO_CHAR
-------------------- --------------------
Thu 19-Dec-2002 10am Fri 20-Dec-2002 08am
Fri 20-Dec-2002 10am Mon 23-Dec-2002 08am <---
Sat 21-Dec-2002 10am Mon 23-Dec-2002 08am <---
Sun 22-Dec-2002 10am Mon 23-Dec-2002 08am <---
Mon 23-Dec-2002 10am Tue 24-Dec-2002 08am
Tue 24-Dec-2002 10am Wed 25-Dec-2002 08am
Wed 25-Dec-2002 10am Thu 26-Dec-2002 08am
Thu 26-Dec-2002 10am Fri 27-Dec-2002 08am
Fri 27-Dec-2002 10am Mon 30-Dec-2002 08am
Sat 28-Dec-2002 10am Mon 30-Dec-2002 08am
Sun 29-Dec-2002 10am Mon 30-Dec-2002 08am
Mon 30-Dec-2002 10am Tue 31-Dec-2002 08am
Tue 31-Dec-2002 10am Wed 01-Jan-2003 08am
Wed 01-Jan-2003 10am Thu 02-Jan-2003 08am
Thu 02-Jan-2003 10am Fri 03-Jan-2003 08am
Fri 03-Jan-2003 10am Mon 06-Jan-2003 08am
Sat 04-Jan-2003 10am Mon 06-Jan-2003 08am
Sun 05-Jan-2003 10am Mon 06-Jan-2003 08am
Mon 06-Jan-2003 10am Tue 07-Jan-2003 08am
Tue 07-Jan-2003 10am Wed 08-Jan-2003 08am
Wed 08-Jan-2003 10am Thu 09-Jan-2003 08am
Thu 09-Jan-2003 10am Fri 10-Jan-2003 08am
Fri 10-Jan-2003 10am Mon 13-Jan-2003 08am
Sat 11-Jan-2003 10am Mon 13-Jan-2003 08am
Sun 12-Jan-2003 10am Mon 13-Jan-2003 08am
Mon 13-Jan-2003 10am Tue 14-Jan-2003 08am
Tue 14-Jan-2003 10am Wed 15-Jan-2003 08am
Wed 15-Jan-2003 10am Thu 16-Jan-2003 08am
Thu 16-Jan-2003 10am Fri 17-Jan-2003 08am
Fri 17-Jan-2003 10am Mon 20-Jan-2003 08am
Sat 18-Jan-2003 10am Mon 20-Jan-2003 08am

31 rows selected.

/**************************************************/


will the job be submitted for 3-times on monday ?
( 1 for sat, 1 for sun and 1 for mon )

Thanks,

I have a package procedure:
package_1.proc_1(date_in IN DATE, argument1 IN NUMBER, argument2 OUT VARCHAR2(20));
I want to schedule a job start to RUN this procedure in early morning tomorrow at 3:00AM, and RUN it at this
time(3:00AM) daily afterwards.

declare
jobno number;
arg_in NUMBER := 1;
arg_out VARCHAR2(2);
BEGIN
DBMS_JOB.SUBMIT(jobno, 'package_1.proc_1(syadate,'||TO_CHAR(arg_in)||','||ls_success||');',
TRUNC(SYSDATE)+1+3/24, 'TRUNC(SYSDATE)+1+3/24');
commit;
END;
/

But I got the error:
declare
*
ERROR at line 1:
ORA-06550: line 1, column 142:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
The symbol "null" was substituted for ")" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 131
ORA-06512: at line 7

BTW, is this NEXT and INTERVAL setup correctly?

Please help

THanks

Thanks for you usful response.

Could you check the NEXT and INTERVAL are correct or not
above according to my criteria please?

THanks

sorry for asking this again
I have 2 procedures hitting the DB at the same time I at the DB level must specify the pririty , You had suggested resource manager , I am not able to follow how do i implement, can u give me a small example explaining the same
Thanks

can DBMS_JOB be used to schedule jobs to be executed x times, starting at a given date with a given period of time in between each execution (is SQL valid for parameter 'intervall')?

there is at least two ways to do this easily

first way -- use an interval input function that returns NULL when you want to stop, the next date otherwise.

second way -- have the procedure itself say when it wants to run next, return NULL when you want to stop, next date otherwise.  Consider:



ops$tkyte@ORA920> create table t ( msg varchar2(50) );
Table created.

ops$tkyte@ORA920> create or replace procedure method1( p_job in number )
  2  as
  3  begin
  4       insert into t values ( systimestamp || ' job = ' || p_job );
  5  end;
  6  /
Procedure created.

ops$tkyte@ORA920> create or replace procedure method2( p_job in number,
  2    <b>                                   p_next_date in out date,
  3                                       p_stop_after in date )</b>
  4  as
  5  begin<b>
  6      p_next_date := sysdate + 1/24/60;
  7      if (p_next_date > p_stop_after)
  8      then
  9          p_next_date := null;
 10      end if;</b>
 11       insert into t values ( systimestamp || ' job = ' || p_job );
 12  end;
 13  /

Procedure created.

<b>and we'll run the jobs every minute for the next 5 minutes:</b>

ops$tkyte@ORA920> column s new_val stop
ops$tkyte@ORA920> select to_char(sysdate+5/24/60,'yyyymmddhh24miss') S from dual;

S
--------------
20030507074933

ops$tkyte@ORA920> variable n number

ops$tkyte@ORA920> begin
  2      dbms_job.submit( :n,
  3                       'method1(JOB);',
  4                       sysdate,<b>
  5                       '(case when sysdate>to_date(''&stop'',''yyyymmddhh24miss'') then null else sysdate+1/24/60 end)' );</b>
  6  end;
  7  /
old   5:                      '(case when sysdate>to_date(''&stop'',''yyyymmddhh24miss'') then null else sysdate+1/24/60 end)' );
new   5:                      '(case when sysdate>to_date(''20030507074933'',''yyyymmddhh24miss'') then null else sysdate+1/24/60 end)' );

PL/SQL procedure successfully completed.

<b>that case expression will be selected from dual when the job is executed.  it'll return NULL when we go past 5 minutes and the job will "unschedule" itself</b>


ops$tkyte@ORA920> begin
  2      dbms_job.submit( :n,
  3                       'method2(JOB,<b>NEXT_DATE,</b>to_date(''&stop'',''yyyymmddhh24miss'') );' );
  4  end;
  5  /
old   3:                      'method2(JOB,NEXT_DATE,to_date(''&stop'',''yyyymmddhh24miss'') );' );
new   3:                      'method2(JOB,NEXT_DATE,to_date(''20030507074933'',''yyyymmddhh24miss'') );' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> commit;

Commit complete.

<b>Here we are letting the job say when it wants to run next procedurally.  our procedure will compute next date and return it.  this is really useful for truly complex intervals like:

run every other tuesday unless it is the 11th of the month, then run wed instead

or something wacky

So, now the jobs are scheduled, we can watch them run</b>

ops$tkyte@ORA920> exec print_table( 'select * from user_jobs' );
JOB                           : 100287
LAST_DATE                     :
LAST_SEC                      :
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 07-may-2003 07:44:33
NEXT_SEC                      : 07:44:33
WHAT                          : method1(JOB);
-----------------
JOB                           : 100288
LAST_DATE                     :
LAST_SEC                      :
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 07-may-2003 07:44:33
NEXT_SEC                      : 07:44:33
WHAT                          : method2(JOB,NEXT_DATE,to_date('20030507074933','yyyymmddhh24miss') );
-----------------
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec print_table( 'select * from user_jobs' );
JOB                           : 100287
LAST_DATE                     : 07-may-2003 07:44:35
LAST_SEC                      : 07:44:35
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 07-may-2003 07:45:35
NEXT_SEC                      : 07:45:35
INTERVAL                      : (case when sysdate>to_date('20030507074933','yyyymmddhh24miss') then null else sysdate+1/24/60 end)
WHAT                          : method1(JOB);
-----------------
JOB                           : 100288
LAST_DATE                     : 07-may-2003 07:44:35
LAST_SEC                      : 07:44:35
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 07-may-2003 07:45:38
NEXT_SEC                      : 07:45:38
WHAT                          : method2(JOB,NEXT_DATE,to_date('20030507074933','yyyymmddhh24miss') );
-----------------
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.
<b>
as you can see the jobs ran after a minute.. as expected.  this went on for a while and then</b>


...

ops$tkyte@ORA920> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec print_table( 'select * from user_jobs' );
JOB                           : 100287
LAST_DATE                     : 07-may-2003 07:48:41
LAST_SEC                      : 07:48:41
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 07-may-2003 07:49:41
NEXT_SEC                      : 07:49:41
INTERVAL                      : (case when sysdate>to_date('20030507074933','yyyymmddhh24miss') then null else sysdate+1/24/60 end)
WHAT                          : method1(JOB);
-----------------
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec print_table( 'select * from user_jobs' );
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920>


<b>they both "disappeared" by themselves.</b> 

GREAT JOB: thank you so much!

""
show me the output of

o you submitting something, eg:

variable n number;
exec dbms_job.submit( :n, 'null;' );
commit;

o the output of show parameter job, eg:

show parameter job

o the output of

set serveroutput on size 1000000
@printtbl8 'select * from user_jobs'

o the output of

exec dbms_lock.sleep( 30 );
@printtbl8 'select * from user_jobs'
""

why do you want dbms_lock.sleep( 30 ) there?

Thanks alot

Tom,
We are running into a problem setting up a batch job.

When I execute the procedure SCHEMA_A.P_RUN_PROC (SQLPlus)
a) from schema_a - runs fine
b) from schema_b - runs fine
c) as a batch job(dbms_job) from schema_a - runs_fine
d) as a batch job(dbms_job) from schema_b - job fails

For case d), the error in the dump file is show below. Why is this error message coming up and what are we doing wrong?

--Setting up the job

variable jobno number
BEGIN
DBMS_JOB.SUBMIT
( JOB => :jobno
, WHAT => 'schema_a.p_run_proc(100);'
, NEXT_DATE => TRUNC(SYSDATE, 'hh' ) + 1/24
, INTERVAL => 'TRUNC(SYSDATE, ''hh'') + 1/24 + 15/(24 * 60)'
);
END;
/

Dump file /u03/app/oracle/admin/beta/bdump/beta_j001_87432.trc
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name: AIX
Node name: abcibmdevl1
Release: 3
Version: 4
Machine: 0003B20S4CA10
Instance name: beta
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 87432, image: oracle@abcibmdevl1 (J001)
*** SESSION ID:(58.14907) 2003-07-18 15:17:09.260
*** 2003-07-18 15:17:09.260
ORA-12012: error on auto execute of job 83
ORA-06550: line 1, column 96:
PLS-00201: identifier 'SCHEMA_A.P_RUN_PROC' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored


Thank you

Tom,
What u have answered is correct.But if i set time daily 6.00 PM and if job fails bcos of some reason my next time should be after 1.00 Hour i.e. 7.00 PM on the same day and at 7PM it's successfull it should start 6PM on next date

Dear Sir,
I tried to create a job which runs every second.It inserts
into a target table .But it fails to do so.Here is the code
what I have tried. 

> desc track
 Name                            Null?    Type
 ------------------------------- -------- ----
 A                                        DATE

SQL> create or replace procedure ins_track
  2  as
  3   begin
  4    insert into track values(to_char(sysdate,'hh24:mi:ss'));
  5   end;
  6  /

Procedure created.

SQL> var v number
SQL> begin
  2   dbms_job.submit(:v,'ins_track;',sysdate,'(sysdate+1/(24*60*60*1000))');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print v

         V                                                                      
----------                                                                      
         6                                                                      

SQL> select * from track;

no rows selected 
// Track table shows no rows.what could be the problem?
 could you please look into this?I use oracle v8.0 personal
edition
Thanks.

 

every second isn't going to fly -- every 5, 10 or more seconds, sure.  every second, too small a time period.

but in your case, set your job_queue_processes and job_queue_interval parameters to get the job queues going 

and don't forget

SQL> commit;

otherwise that job just isn't "visible" to anyone but you (eg: the job queues cannot see it until you commit)


one second is simply:

sysdate+1/24/60/60

 

Hi Tom,

We are running similiar problem that job queue system is not functioning.  Our production DBA suggest to rebuild AQ - job queue subsystem, I do not where to start.  Please help.
Please see the following, 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> spool c:\job_queue_log.txt;
SQL> show parameter job;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_job_queue_interval                  integer     60
job_queue_processes                  integer     100
SQL> ed;
Wrote file afiedt.buf

  1* create table test_job_queue (id number, content varchar2(100))
SQL> /

Table created.

SQL> ed;
Wrote file afiedt.buf

  1  declare
  2     p_job integer;
  3  begin
  4      dbms_job.submit( p_job,
  5      'begin'||chr(10)||
  6        'insert into test_job_queue values(1,''test number one'');'||chr(10)||
  7        'end;',
  8       sysdate);
  9      commit;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> exec dbms_lock.sleep(600);


SQL> select * from test_job_queue;

no rows selected

SQL> /

no rows selected

SQL> select LAST_DATE, NEXT_DATE, BROKEN, INTERVAL, WHAT   from all_jobs where job = (select max(job
) from all_jobs);

LAST_DATE NEXT_DATE B
--------- --------- -
INTERVAL
--------------------------------------------------------------------------------
WHAT
--------------------------------------------------------------------------------
          09-DEC-03 N
null
begin
insert into test_job_queue values(1,'test number one');
end;

We have more than five recurring jobs in the development server, all stop working one month ago.  We are under window 2000 environment.

Thank you very much for help.  We really appreciate it.

 

Hi Tom,

Thank you very much for your quick response.

I have tried your approach. The job queue system still not react (dead).

Please help more...

Hi Tom,

Thanks for your help.

_job_queue_interval is intentionally-used, because oracle 9i has depreciated job_queue_interval, oracle will not accept job_queue_interval anymore or it will be ignored anyway.

We did restart the instance.

It is still not functioning.

Thanks for help.

my point is -- show parameter WILL NOT SHOW _ PARAMETERS AT ALL

ops$tkyte@ORA9IR2> show parameter job
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
ops$tkyte@ORA9IR2>


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
 
ops$tkyte@ORA9I> show parameter job
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1
ops$tkyte@ORA9I>



so tell me -- what wacky things have you done to your system to cause _ parameters to appear.

has someone been playing with the data dictionary????

 

Hi Tom,

Thanks for help.

Since oracle 9i depreciated job_queue_interval, initially, we did not have this parameter.  Because our job queue system of oracle is not functioning on our development server, we intentionally add job_queue_interval, oracle does not accept job_queue_interval, so we add _job_queue_interval instead.  We have not touch data dictionary at all.

SQL> ed;
Wrote file afiedt.buf

  1* select name from v$parameter where name like '%job_queue%'
  2  /

NAME
------------------------------------------------------------job_queue_processes
_job_queue_interval

SQL> show parameter job;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------_job_queue_interval                  integer     1
job_queue_processes                  integer     50

job_queue_interval is basically depreciated in oracle 9i.

Thanks for advice.

 

Hi Tom,

Thank you very much for your precious time.

Here is the output:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

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

Session altered.

SQL> select what, broken, next_date, last_date, sysdate from user_jobs;

WHAT
--------------------------------------------------------------------------------
B NEXT_DATE         LAST_DATE         SYSDATE
- ----------------- ----------------- -----------------
begin
insert into test_job_queue values(1,'test_job_queue1');
end;
N 11-dec-2003 15:29                   11-dec-2003 15:48

Still not working.

By the way, since oracle 9i increases the job_queue_processes up to 1000 from 36 (8i), what is your general suggestion to set that value considering row-level trigger to fire jobs to send out emails?  Right now, in our test and production environment, we use job_queue_processes = 5, we prefer the value to be a bit larger, but fear that will more likely to cause email jam...  What could be the best value to strike a balance, generally-speaking?

Please advice.  Thanks for help. 

Hi Tom,

Thanks for help.

Here is the result.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select count(*) from dba_jobs_running;

  COUNT(*)
----------
         0

Thanks. 

Hi Tom,

Thank you very much for your help, I sincerely appreciate it.

We have contacted the oracle support, first, oracle asks us to install the remote diagnose agent...finally,

exec dbms_ijob.set_enabled(true);

The job queue system starts to work immediately.

Thanks.

I am trying to run this stored procedure. Tsa_ArchiveDoc( message IN Varchar2 ,DocID IN Number ) is the procedure I am trying to run from a job.
The job is
CREATE OR REPLACE procedure schedule_archivejob(DOCID IN number, name in varchar2)
as
a number ;
b number ;
doccomment varchar2(64);
begin
doccomment := 'Auto Archive';
dbms_job.submit(a, 'Tsa_ArchiveDoc("'||doccomment||'",'||DOCID||');',sysdate + b/24 ,null);
dbms_output.put_line(doccomment);
end;
I am getting this error
'Auto Archive must be declared .
How to do this

I have this procedure - I am trying to update quarter start date in the table with this procedure and if someone changed the quarter start date I am resubmitting the job in this same procedure to be run every 3 months from the date entered ( passed to the procedure).

CREATE OR REPLACE PROCEDURE pr_date_update_prdQuarter
(inPeriodQuarter int )
IS
dtPeriodQuarter date;
v_jobnum number;
dtRunDate date;
inRunChk int;

Begin


-- inPeriodQuarter is the start of quarter jan= 1 dec=12
-- inAfterBefore not needed

/** Put the date for weekperiod back to last sunday**/

dtPeriodQuarter := TO_DATE(TO_CHAR(last_day( to_date(inPeriodQuarter, 'mm') -1), 'YYYY-MM-DD'), 'YYYY-MM-DD') + 1;

Update mwebSettings
Set SET_REG_VALUE = dtPeriodQuarter
Where SET_REG_NAME = 'DATEPERIODQUARTER';



-- to setup new job schedule or update the existing one


dtRunDate := to_date(inPeriodQuarter, 'mm') ;

-- check if the job already exists
select count(*) into v_jobnum from user_jobs
where what='pr_date_update_prdQuarter(inPeriodQuarter);';

If v_jobnum > 0 then
-- check if the inPeriodWeek is changed only then change the job
Select to_char(next_date,'mm') into inRunChk from user_jobs
where what='pr_date_update_prdQuarter(inPeriodQuarter);';

If inRunChk <> inPeriodQuarter then
DBMS_JOB.CHANGE(v_jobnum, 'pr_date_update_prdQuarter(inPeriodQuarter);',
dtRunDate, ADD_MONTHS(dtRunDate,3));
End if;
Else
dbms_job.submit(v_jobnum, 'pr_date_update_prdQuarter(inPeriodQuarter);',
dtRunDate, ADD_MONTHS(dtRunDate,3));
End if;



COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20002, SQLCODE, TRUE);
raise_application_error(-20003, SQLERRM, TRUE);
ROLLBACK;
End pr_date_update_prdQuarter;
/

When I execute this I get following error
SQLWKS> execute pr_date_update_prdQuarter (2)
ORA-23319: parameter value "01-MAY-04" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at "V52DEVOR.PR_DATE_UPDATE_PRDQUARTER", line 44
ORA-06512: at line 2

What am I doing wrong in this ?
Thanks

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQLWKS> CREATE OR REPLACE PROCEDURE pr_date_update_prdQuarter
2> (inPeriodQuarter int )
3> IS
4> dtPeriodQuarter date;
5> v_jobnum number;
6> dtRunDate date;
7> inRunChk int;
8>
9> Begin
10>
11>
12> -- inPeriodQuarter is the start of quarter jan= 1 dec=12
13> -- inAfterBefore not needed
14>
15>
16> dtPeriodQuarter := TO_DATE(TO_CHAR(last_day( to_date(inPeriodQuarter, 'mm') -1), 'YYYY-MM-DD'), 'YYYY-MM-DD') + 1;
17>
18> Update mwebSettings
19> Set SET_REG_VALUE = dtPeriodQuarter
20> Where SET_REG_NAME = 'DATEPERIODQUARTER';
21>
22>
23>
24> -- to setup new job schedule or update the existing one
25>
26>
27> dtRunDate := to_date(inPeriodQuarter, 'mm') ;
28>
29> -- check if the job already exists
30> select count(*) into v_jobnum from user_jobs
31> where what='pr_date_update_prdQuarter(inPeriodQuarter);';
32>
33> If v_jobnum > 0 then
34> -- check if the inPeriodWeek is changed only then change the job
35> Select to_char(next_date,'mm') into inRunChk from user_jobs
36> where what='pr_date_update_prdQuarter(inPeriodQuarter);';
37>
38> If inRunChk <> inPeriodQuarter then
39> DBMS_JOB.CHANGE(v_jobnum, 'pr_date_update_prdQuarter('||inPeriodQuarter||');',
40> dtRunDate, ADD_MONTHS(dtRunDate,3));
41> End if;
42> Else
43> dbms_job.submit(v_jobnum, 'pr_date_update_prdQuarter('||inPeriodQuarter||');',
44> dtRunDate, ADD_MONTHS(dtRunDate,3));
45> End if;
46>
47>
48>
49> COMMIT;
50>
51> End pr_date_update_prdQuarter;
52> /
Statement processed.
SQLWKS> execute pr_date_update_prdQuarter (2) ;
ORA-23319: parameter value "01-MAY-04" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at "V52DEVOR.PR_DATE_UPDATE_PRDQUARTER", line 43
ORA-06512: at line 2


You are right.. the error lines are DBMS_JOB.submit.

dbms_job.submit(v_jobnum, 'pr_date_update_prdQuarter('||inPeriodQuarter||');',
dtRunDate, ADD_MONTHS(dtRunDate,3));

The problem I have here is, procedure gets the quarter month say 3 which will be March.. The quarter is not 'Q' as per the database. Its decided by end user.

Also they can run this procedure for the 1st time on say 21st Apr 2004 and want to set the quarter start month to March and want to run this job every 3 months from March and not sysdate, so I cannot do (sysdate,3) here.

They can run this job on say 21st Apr 2004 and want to set the quarter start date to say June 1st 2004.. I would not know when they will run this procedure and what month their quarter starts.

So I was passing in varaible. Can I do it this way ?

I have 3 different procedures- one for month, one for week and this one is for quarter.
I have same problem with weekly and monthly procedures. I can paste them here is it can help in this.. I thought if you can help me with passing the variable value to this dbms_job it would solve problem in all 3 procedures and would be great..
Thanks

I think I crossed most of the issues I had with your book except 1 now.

SQLWKS> CREATE OR REPLACE PROCEDURE pr_date_update_prdweek
2> (P_JOB IN NUMBER, p_next_date in OUT date, inPeriodWeek int, inAfterBefore int)
3> IS
4> l_sunday varchar2(255) default to_char( to_date( '20040104', 'yyyymmdd' ), 'day' );
5> l_monday varchar2(255) default to_char( to_date( '20040105', 'yyyymmdd' ), 'day' );
6> l_tuesday varchar2(255) default to_char( to_date( '20040106', 'yyyymmdd' ), 'day' );
7> l_wednesday varchar2(255) default to_char( to_date( '20040107', 'yyyymmdd' ), 'day' );
8> l_thursday varchar2(255) default to_char( to_date( '20040108', 'yyyymmdd' ), 'day' );
9> l_friday varchar2(255) default to_char( to_date( '20040109', 'yyyymmdd' ), 'day' );
10> l_saturday varchar2(255) default to_char( to_date( '20040110', 'yyyymmdd' ), 'day' );
11> l_next_date date default p_next_date;
12> dtPeriodWeek date;
13> v_jobnum number;
14> dtRunDate date;
15> inRunChk int;
16> inJobCount int;
17>
18> Begin
19>
20>
21> -- inPeriodWeek is the day of the week, Sun =1 , Mon=2 etc
22> -- inAfterBefore is the day after or before today After =2, Before =1, default 1
23>
24> /** Put the date for weekperiod back to last sunday**/
25>
26> If inAfterBefore = 1 then
27>
28> dtPeriodWeek := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD') -1, l_sunday) - 7;
29>
30> Update mwebSettings
31> Set SET_REG_VALUE = dtPeriodWeek
32> Where SET_REG_NAME = 'DATEPERIODWEEK';
33>
34> Else
35> dtPeriodWeek := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD')- 1, l_sunday) ;
36>
37> Update mwebSettings
38> Set SET_REG_VALUE = dtPeriodWeek
39> Where SET_REG_NAME = 'DATEPERIODWEEK';
40> End if;
41>
42> -- to setup new job schedule or update the existing one
43>
44> If inPeriodWeek =1 then
45> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_sunday) ;
46> Elsif inPeriodWeek =2 then
47> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_monday) ;
48> Elsif inPeriodWeek =3 then
49> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_tuesday) ;
50> Elsif inPeriodWeek =4 then
51> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_wednesday) ;
52> Elsif inPeriodWeek =5 then
53> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_thursday) ;
54> Elsif inPeriodWeek =6 then
55> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_friday) ;
56> Elsif inPeriodWeek =7 then
57> dtRunDate := Next_Day(to_date(to_char(sysdate ,'YYYY-MM-DD'),'YYYY-MM-DD'), l_saturday) ;
58> End if;
59>
60> p_next_date := dtRunDate + 7;
61>
62> -- check if the job already exists
63> select count(*) into inJobCount from user_jobs
64> where WHAT like 'pr_date_update_prdweek%';
65> If inJobCount > 0 then
66> -- check if the inPeriodWeek is changed only then change the job
67> Select to_char(next_date,'D'), JOB into inRunChk, v_jobnum from user_jobs
68> where WHAT like 'pr_date_update_prdweek%';
69>
70> If inRunChk <> inPeriodWeek then
71> dbms_job.change(v_jobnum, 'pr_date_update_prdweek(JOB, NEXT_DATE,'||inPeriodWeek||','||inAfterBefore||');');
72> End if;
73>
74> End if;
75>
76>
77>
78> COMMIT;
79>
80> End pr_date_update_prdweek;
81> /
MGR-00072: Warning: PROCEDURE PR_DATE_UPDATE_PRDWEEK created with compilation errors.
SQLWKS>
SQLWKS> show errors
Errors for PROCEDURE PR_DATE_UPDATE_PRDWEEK:
LINE/COL ERROR
--------------------------------------------------------------------------------
71/3 PLS-00306: wrong number or types of arguments in call to 'CHANGE'
71/3 PL/SQL: Statement ignored


How do I pass in constants ( variable values) in my dbms_job
dbms_job.change(v_jobnum, 'pr_date_update_prdweek(JOB, NEXT_DATE,'||inPeriodWeek||','||inAfterBefore||');');


I think I have problem with my quotes somewhere but I can't figure out.. I tried everything.
Thanks
Sonali

Hi Tom,

If I have 10 batch jobs in a job queue which at this point of time are not running, what kind of oracle and CPU resources they consume?

Thanks,

Sven

Ok,
I have asked this because I see in a wait statistics that "jobq slave wait" is about 45% of all waits.
What does this means?

Thanks,

sven

dbms_job.submit, 'what' parameter

Hi tom,
I need to create and submit a job in a procedure that accepts ao a pl/sql table. How can I get that pl/sql table in the varchar2 "what" parameter of the job?

something like this:

CREATE OR REPLACE package testjob is
TYPE t_my_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
PROCEDURE show_it(p_tab in t_my_table);
END testjob;

create or replace package body testjob is
PROCEDURE show_it(p_tab in t_my_table) is
BEGIN
dbms_output.put_line(p_tab(1));
END show_it;
end testjob;




Procedure create_testjob(p_tab testjob.t_my_table)
IS
jobid number(38);
BEGIN
dbms_job.submit(jobid,
'begin
testjob.show_it(t_tab);
end;',
sysdate + 1/(24*60*60), '' );
END;

Thanks,

1st thanks to your book page 1068 from expert one on one..and this site.

I have a procedure where I need to find the next_date based on user input.. I got all that working..

I do this in the procedure only 1st time-
-- check if the job already exists
select count(*) into inJobCount from user_jobs
where JOB=P_JOB;

If inJobCount = 0 then

dbms_job.submit(v_jobnum, 'pr_date_update_restart(JOB, NEXT_DATE);');
dbms_job.next_date(v_jobnum,p_next_date);
End if;

The problem is when the export import is done this job information is gone...
Is there any way to keep it during the import or how do I do it later on ?
Thanks

The problem I have now is during the export and import process job schedules do not come in.. atleast I don't know how to keep the job schedules coming over during import..do you know a way ??

So other choice was to submit those jobs again after the import.. but the problem with that would be in this perticular case.. is I would not know the next_date from the job as its entered by user and put in the job schedule .. I did this with your help.. in this link I had some questions about it some time back, your book also helped me..

Everything is working now except when the import is done in some other schema I loose that next_date info.
I hope I explained it better this time..
thanks

When I do the export from schema1 and import into schema2 say they are in the same database..

I get this error during the import..its because the job number is used by schema1 and is not unique..
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN DBMS_JOB.ISUBMIT(JOB=>2865,WHAT=>'pr_refresh_mvworkrequestcalcs;',N"
"EXT_DATE=>TO_DATE('2004-08-27:07:59:21','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>"
"'SYSDATE + 2.5/24',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 97
ORA-06512: at line 1


So I figured I will need to just run the job submit script again in schema2..but then I do not have next_date information for some jobs where that date is decided by user. well I can get this value from oracle import logs, but clients will not like that idea...
So I was looking for some easier way to get around this problem, can you help ?

Hi Tom,
i think the custom solution you suggested could be implemented by using the procedure:

dbms_job.user_export(job in binary_integer , mycall in out vachar2)

here mycall has the Job Submit String:

but to use this script, you have to be logged-in as the Job-Owner.

and all you get is a string (that would be trivial to make from a simple query against dba_jobs) that still needs to be rewritten as it has the job id in it just like the other stuff.

so, you are back to "you need to write a custom procedure to copy a job from schema to schema"


ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'null;', sysdate, 'sysdate+1' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> variable s varchar2(2000)
ops$tkyte@ORA9IR2> exec dbms_job.user_export( :n, :s )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print s
 
S
-------------------------------------------------------------------------------
dbms_job.isubmit(job=>108616,what=>'null;',next_date=>to_date('2004-09-01:08:06
:22','YYYY-MM-DD:HH24:MI:SS'),interval=>'sysdate+1',no_parse=>TRUE);



this entry point is designed for use by EXP.  it would be easier (in my opinion) to just query dba_jobs to get the necessary information to pass as inputs to dbms_job.submit with an empty job_id (you have to change isubmit to submit, parse out the job=>108616,  -- why not just query what, next_date, interval and make a call?) 

Hi Tom,

you're my last hope as I didn't find anything in the docs and nobody answers my question on Metalink:

I've submitted a job using DBMS_JOB.SUBMIT with no interval (one-time job) and it have been executed correctly. But after that the row with the job has disappeared from USER_JOBS. Is that correct behaviour? I cannot find any info about how long the job should be visible in this view after the successful completion.

Here is all my steps:

SQL> select sysdate from dual;

SYSDATE
-------------------
2004-10-12 09:58:00

SQL> var v_job_num number;

SQL> exec  DBMS_JOB.SUBMIT( :v_job_num, 'DBMS_STATS.GATHER_SCHEMA_STATS( ''SHMYG'');' );

 V_JOB_NUM
----------
         2

SQL> commit;
SQL> select * from user_jobs;
SQL> select job, lowner from sys.job$;
SQL> select LAST_ANALYZED from user_tables;

LAST_ANALYZED
-------------------
2004-10-12 09:58:14
2004-10-12 09:58:14
2004-10-12 09:58:14
2004-10-12 09:58:14
2004-10-12 09:58:14
2004-10-12 09:58:14
2004-10-12 09:58:15
2004-10-12 09:58:15
2004-10-12 09:58:15
2004-10-12 09:58:15
2004-10-12 09:58:15

I thought that all the jobs were stored like in Unix cron. But looks that they're not. If it's correct, how can we audit job execution?

Thanks. 

Thanks, Tom. But I'd like to ask you what is the best way to do that?

I tried to apply audit for SYS.JOB$:

SQL> AUDIT INSERT ON sys.job$

but it didn't work. Why? I've enabled tracing for my session and after DBMS_JOB.SUBMIT I can see in my trace file:

INSERT INTO SYS.JOB$ ( JOB,LOWNER,POWNER,COWNER,NEXT_DATE,INTERVAL#,FLAG,WHAT,
  NLSENV,ENV )
VALUES
 ( :b1,:b2,:b3,:b4,NVL(:b5,TO_DATE('4000-01-01','YYYY-MM-DD')),NVL(:b6,'null')
  ,:b7,:b8,:b9,:b10  )

Is this possible to audit name$ database objects?

Thanks. 

Thanks - the example with Unix is very comprehensie. And I won't touch 'sys' objects anymore :-). But anyway - what event/object can I apply audit on to audit DBMS_JOB usage? I can't see any possibility except for

SQL> audit execute on dbms_job;

but information in AUD$ is not very useful. Should I create a wrapper for DBMS_JOB? 

I have tried to submit a job as user: system.
But for some reason it doesn't run unless I submit it as another user. I cant even see the job as system.

Select job from all_jobs;

0 row returned as system

How do I make a job as system ? (I tried but it failed)

I know all articles are good, but this is really wonderfull.

job with different log_user,priv_user,schema_owner;
such as this:

select log_user,priv_user,schema_user from dba_jobs;

LOG_USER PRIV_USER SCHEMA_USER
------------------------------ -----------------
EUOPP EUOWNER SYSTEM

It would be horribly messy and confusing, but it can be done (why would be the real question :)

ops$tkyte@ORA9IR2> grant create session, create procedure to a;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to b;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session to c;
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create procedure p
  2  as
  3  begin
  4      null;
  5  end;
  6  /
 
Procedure created.
 
a@ORA9IR2> grant execute on p to b;
 
Grant succeeded.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> create or replace procedure p
  2  as
  3     n number;
  4  begin
  5      execute immediate '
  6          declare
  7             n number;
  8          begin
  9             execute immediate ''alter session set current_schema=a'';
 10             dbms_job.submit(n,''p;'');
 11          end;';
 12  end;
 13  /
 
Procedure created.
 
b@ORA9IR2> grant execute on p to c;
 
Grant succeeded.
 
b@ORA9IR2>
b@ORA9IR2> @connect c/c
b@ORA9IR2> set termout off
c@ORA9IR2> set termout on
c@ORA9IR2> exec b.p
 
PL/SQL procedure successfully completed.
 
c@ORA9IR2> @connect b/b
c@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> select log_user, priv_user, schema_user from user_jobs;
 
LOG_USER PRIV_USE SCHEMA_USE
-------- -------- ----------
C        B        A
 

 
 

Would this not be much easier to accomplish if Oracle were to make standard and publish the documentation for dbms_ijob. I have taught myself some key procs like run, broken, and etc. We DBAs have been given the ability to grant privileges to objects in various schemas into which we are not connected. So why not give us the ability to use a documented version of dbms_ijob?

Tom,

I found this thread somehow related to my problem, but still I need your expertise for my specific issue:

About 2 weeks ago I had to rename an existing database: ORACLE_SID, NAME and GLOBAL_NAME. I followed the steps accordingly and at the end the database was up and everything was running ok (at least, looks like ..).

It doesn't look to me to have anything in common with what I did, however since then, every day I got couple of trc files in my bdump with the following content:
***********************************
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /.../app/oracle/product/9.2.0
System name: SunOS
Node name: Node_Name1
Release: 5.8
Version: Generic_108528-29
Machine: sun4u
Instance name: ECP101DA
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 238, image: oracle@Node_Name1 (J000)

*** SESSION ID:(20.9135) 2005-03-17 10:06:41.932
*** 2005-03-17 10:06:41.932
ORA-12012: error on auto execute of job 88
ORA-06550: line 1, column 96:
PLS-00201: identifier 'NAME_OF_PKG_1' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
***********************************************

Maybe is important to mention the following, even that some of them might have nothing to do with the problem:

1) 'NAME_OF_PKG_1', 'NAME_OF_PKG_2' and so on, have public synonyms

2) other than the owner of the packages, another Oracle account has the right to execute these packages through role.

3) I do have about 11 Oracle User Jobs (calling certain procedures within above packages) which seems to run ok. I checked them and the jobs are running fine as scheduled and with no errors. Plus, my Oracle Jobs have a job_id different from the one mentioned in trc file.

4) I do not have anything in
select * from dba_jobs_running;

5) I bounce the database once.

Please advice.
Thank you

so, come into the database -- log in as the user that runs this job and tell us if you can 

SQL> set role none;
SQL> desc name_of_pkg_1;

if not, this user has lost their ability to run that package. 

Aditional to my previous intervention, I have to come up with some new details:

If I say
select job, what from user_jobs;
in the schema where the packages belongs to I got my 11 jobs working fine.

If I connect as sysdba and I say
select job, what from dba_jobs;
I got my above 11 jobs plus another "phantom" 11 jobs exactly with the same "what", with the job_id specified in trc file.
Some of these 11 phantom jobs are broken some not.

If I try to remove them either as sysdba or as owner I got
ORA-23421.

How can I get rid of the phantom jobs ?

Thank you

Hi,

How, within PL/SQL, could I emulate the Java Thread.sleep() method? e.g. how could I code a procedure to update a table, say, 15 seconds after the procedure was called (with, perhaps, the *sleep* interval having been passed into the procedure)?

I realise that DBMS_JOB could be used for this, but is there a better way (it would be interesting to know if Java can be employed, here)?

Thanks in advance for any help.

Depending on how long you want to sleep for, you might want to reconsider that. As documented at Jonathan Lewis' website
</code> http://www.jlcomp.demon.co.uk/faq/sleep.html <code>
DBMS_LOCK.SLEEP is not exactly very accurate.
However, see the very bottom of that page for an accurate method still involving pure PL/SQL!

Excellant - gave me enough info to figure it out myself;

Tom,

I sometimes see this in my sessions

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'
during a job is running? I am trying to find that
why is that the case. Any thoughts

it is your NLS settings, language, date format, currency settings and the like.

If you:

ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'null /* default */;' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';
 
Session altered.
 
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'null /* changed */;' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select what, nls_env from user_jobs;
 
WHAT
------------------------------
NLS_ENV
-------------------------------------------------------------------------------
null /* default */;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURREN
CY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_L
ANGUAGE='AMERICAN' NLS_SORT='BINARY'
 
null /* changed */;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURREN
CY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss
' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
 


The job is to run in the same environment it was created in, so at runtime, it'll pick up these values and set them. 

Tom

In your example,

dbms_job.submit( :n,
3 'method1(JOB);',
4 sysdate,

.
.
.

where does the variable "JOB" from, which is it documented ?

Thanks

Dear Tom,

I have schema'a named schema_AA , schema_BB
I have a table named table_AA
I have a procedure name proc_AA

Now when I try to access
In schema_BB
begin
schema_AA.proc_AA
end;

Everything is fine but when I do the same in
schema_AA
begin
schema_AA.proc_AA
end;

it says must be declared.

now If I run the same thing without the schema name it works fine.i.e
In schema_AA
begin
proc_AA
end;

Please advise whats going on.

Thankyou very much.

Consider Schema_AA = AA (i.e named as AA)
and Table_AA = AA (i.e table also named as AA)

Thankyou very much.

:-)
table and schema name is same.
I found it surprising that Oracle got confused and gave an error, to resolve it anyways someone suggested me Synonym and it worked.

Thankyou

Dear Tom,

Below I have enclosed two pieces of codes which are in JAVA. These two codes call PL/SQL procedures through {call DBMS_JOB.SUBMIT(?,?,?)}

Scenario 1
----------
<sql Id="dbmsJobSubmit">
<query>
{call DBMS_JOB.SUBMIT(?,?,?)}
</query>

This takes 3 parameters

1. oCallableStmt.registerOutParameter(1, java.sql.Types.INTEGER);


2. String strProcCall = "Procedure_Name_One(To_Date('" +
a_oPaymentSources.getDueFromDate() + "','yyyy-mm-dd'),To_Date('" +
a_oPaymentSources.getDueToDate() + "','yyyy-mm-dd'),'" +
a_oPaymentSources.getBankCode() + "',To_Date('" +
a_oPaymentSources.getDeductionDate() + "','yyyy-mm-dd')," +
a_oPaymentSources.getBatchNo() + ",'" +
a_oPaymentSources.getUserInfo().uid + "','" +
a_oPaymentSources.getFileName() + "');";

3. oCallableStmt.setDate(3, EElixirUtils.getSystemDate());

When the submit button in the application is clicked this job runs to completion.

************************************************************

Scenario 2
----------


1. oCallableStmt.registerOutParameter(1, java.sql.Types.INTEGER);

2. String strProcCall = "Procedure_name_Two('" +
a_oPaymentSources.getFileName() + "','" +
a_oPaymentSources.getBankCode() + "');";

3. oCallableStmt.setDate(3, EElixirUtils.getSystemDate());

When the submit button is clicked the job is formed and placed in the queue. The scheduler tries to run the job but fails every time. When I run the same procedure from the sql prompt - It runs to completion. What could be the reason.

************************************************************

Hi Tom,

I am trying to create a procedure

create or replace test
is
begin
insert into ......
.......
dbms_lock.sleep (30);
insert into .......
.....
end;
/

When I try to compile this procedure I am getting the following error

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: Statement ignored
6/1 PLS-00201: identifier 'DBMS_LOCK' must be declared

But I try to do a desc dbms_lock, it works fine.

What is the error I am doing?

Thanks.

Hi tom,

I need your help. We have a 9i database and werun a lot of processes as part of out daily routine. One such process is creating a lot of sessions by which the sessions limit is exceeding and no new connections are available. We need to shutdown that process and after a while again the problem arises.

I've set the Job_queue_process to 5 (it was 20 earlier)
Still its generating trace files in bdump.

I am copying here a sample of trace file.


Dump file /apps1/mdbdata_new/bdump/mdbdata_j002_2241.trc
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /oracle/product/9.2.0
System name: SunOS
Node name: RDOCNMDBBLR1
Release: 5.9
Version: Generic_118558-06
Machine: sun4u
Instance name: MDBDATA
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 2241, image: oracle@RDOCNMDBBLR1 (J002)

*** 2005-08-11 06:48:20.160
*** SESSION ID:(19.7325) 2005-08-11 06:48:20.142
kwqjswproc: excep After loop: ASSIGNING to self
*** 2005-08-11 07:07:24.769
*** SESSION ID:(19.7327) 2005-08-11 07:07:24.769
kwqjswproc: excep After loop: ASSIGNING to self

Please tell me how to control the situation.

Thanks in Advance

Pradikan

Hi Tom,

Thanks for the help.. I'll take a look into the program..

Pradikan

This identified our problem precisely and we were able to move forward by granting the privilege separately.
Thanks.

What might be the cause?

SQL> create or replace procedure proc is
  2   begin
  3  dbms_output.put_line(sysdate);
  4  end;
  5  /

Procedure created.

SQL> begin 
dbms_job.submit(:jobno,'proc',sysdate,'sysdate+1');
commit;
end;
/  2    3    4    5  
begin
*
ERROR at line 1:
ORA-06550: line 1, column 98:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 2
 

Tom,

When I submit below job, it runs immediately and schedule it for next time.

DECLARE
v_jobnum NUMBER;
BEGIN
DBMS_JOB.SUBMIT (v_jobnum,'OPL_REF03_BULKCLOSE;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''FRIDAY''))) + 12.25/24');
END;
/

I want to schedule job for next time but don't want it to run now. Even when I change sysdate to trunc(sysdate) then also it runs immediately and schedule for next time.

How can I avoid to run job at the time of submission.

Oracle version is 8.1.7.4

ops$tkyte@ORA10GR2> variable n number
ops$tkyte@ORA10GR2> exec dbms_job.submit( :n, 'null;', trunc(next_day(sysdate,'friday'))+12.25/24, 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''FRIDAY''))) + 12.25/24');


instead of passing in sysdate, pass in the date you want to use for the first run. 

Tom, can I alter a job created with another user??

Tom,

We have got a very strange situation with the jobs that HTML DB runs internally.

Here are the details
what -> begin wwv_flow_job.run_internal_job(p_job => JOB); end;

SCHEMA_USER->FLOWS_010600
PRIV_USER ->FLOWS_010600
LOG_USER ->SYS

and I'm getting error

ERROR at line 1:
ORA-23421: job number 1436 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 258
ORA-06512: at line 1

I've checked all permissions etc....
I tried your solution above to recreate the jobs.
But still failing even when I try to re-run with sys/flows%

Please advice.

With Regards,
Shalini

where are you getting that error
-> Alert log

what "causes" it-> Thats what I'm trying to find out

is there in fact a job 1436?
-> Yes there is,that job is there in dba_jobs as well as user_jobs and is with BROKEN='Y'

I dont see any reason for that job getting failed -
but still if I give dbms_job.run(jobno) I get such error !!!

With Regards,
Shalini

the job is broken, why do you expect it to run successfully?

but anyway, show us that the job is there, and that dbms_job.run is failing, something like this:

ops$tkyte@ORA10GR2> select job, what, broken from dba_jobs where job = :n;
 
       JOB WHAT                           B
---------- ------------------------------ -
        24 null;                          Y
 
ops$tkyte@ORA10GR2> exec dbms_job.run(:n);
 
PL/SQL procedure successfully completed.
 

and the job is failing and constantly we are getting errors in alert log. At one point,we have around 100s of broken jobs with almost same description like


select job, what, broken from dba_jobs where broken='Y' ;

1592
begin wwv_flow_job.run_internal_job(p_job => JOB); end;
Y

LOG_USER=HTMLDB_PUBLIC_USER

So ran follwoing with HTMLDB_PUBLIC_USER
SQL>  exec dbms_job.run(1592);
BEGIN dbms_job.run(1592); END;

*
ERROR at line 1:
ORA-23421: job number 1592 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 258
ORA-06512: at line 1

SCHEMA_USER/PRIV_USER=FLOWS_010600
so ran following with FLOWS_010600
SQL>  exec dbms_job.run(1592);
BEGIN dbms_job.run(1592); END;

*
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 1

And alert log is showing
ORA-23421: job number 1592 is not a job in the job queue



The document in Oracle says this(run_internal_job) is a procedure supposed to run within the wwv_flow_job package. But dont know why/how it is failing and what to do for getting workaround permanently. 

Hi Tom

As usual I am very happy to go through the site and links to learn about new things from here. The following line is very interesting to me as these type of settings will not be directly visible to sort out the issues like the one in the thread. " exec dbms_ijob.set_enabled(true); "
I think by default this paramater will be true in the database. In case, if we want to check / set where can we update this parameter?

2. By using the command "Select * from dba_jobs_running" we can get to know the process id of the job being run. I have observed that as one process starts, it opens other sessions to complete the job parallelly. If we want to kill / stop a process which was running, how can we kill that process? If we kill the main process, will the sub processes (which were automatically generated) will be killed? If not, how to identify them and kill them?

Thanks and Regards
Raghav

Hi Tom,
i am not sure if dbms_job.submit Procedure is doing an
implict commit every time after Executing a certain Job.

For Example i did the following test:

create table test( id number) ;

declare
job number ;
begin
dbms_job.submit(job,'insert into test values (1);',
sysdate, 'sysdate + 1/24/60');
commit;
end;
/

Note that my Job, which is 'insert into test values (1);'
does not have a Commit.

The funny thing is that, i could see the rows from another
session although i had only an Insert without a Commit.

select * from test;

Is this the expected behaviour?

Thank you Tom,

i have been using dbms_job for years, but i had not known that there is a commit after a Job is run.

so this means, there is no need to put a commit or rollback in any Procedure which is to be run as dbms_job.

Hi Tom,

I have problem with job.
we work with oracle 8.1.7.4 on Aix 4.3.3 OS

job_queue_interval 60
job_queue_processes 5

I have a application witch creates receipt using jobs.

Sometimes, receipt are not generated and We don't know exactly why (jobs disappear ,not executed or are badly executed) ...

1. It is possible that SNPX processes loose jobs or
run not properly ?

2.Is there any way of following a job since its creation and until its execution ?

In fact, I want to know when a job arrived in queue and the hour when it is carried out by the processes snp.

Example :
Job's creation (date, time,what)
then :
job set in queue (date in queue)
and finally :
job execution

First idea is to create trigger on sys job objects, but it's forbidden ...


Thank in advance

Hi Tom,

At first, Thanks a lot for your help.

This is a very good idea but, it is almost inapplicable in my case.
The job is started in more than one ten functionalities of the application, which wants to say that it is necessary that I modify much thing to test correctly.
I rather wish to find a method to directly trace the jobs in the tails (if possible)
thanks by advance

Ok, I 'm going to correct the code immediately , and modify application too.
Thanks again for your help

i need job and i am 13 years old so can i have a job. my famley is vary poor beacaus i was aboted by a cousin. so can i have a job an i think you relly need me i am nice.

 Hi Tom,
If the following is created and run as a user with dba role i get the error message. But works fine as sys. 
 SQL> Declare
  2  N number;
  3  BEGIN
  4   DBMS_JOB.SUBMIT(JOB => n,
  5   WHAT =>' BEGIN
  6  For x in  (select username  from sys.dba_users
  7  where username IN(SELECT DISTINCT(OWNER) FROM sys.DBA_OBJECTS) and  account_STATUS=''OPEN'')
  8  LOOP
  9  dbms_stats.gather_schema_stats(OWNNAME =>x.username,
 10  ESTIMATE_PERCENT => 20,
 11  METHOD_OPT =>''FOR ALL COLUMNS SIZE 75'',
 12  DEGREE => 1,
 13  CASCADE => TRUE);
 14  END LOOP;
 15  commit;
 16  END;',
 17   NEXT_DATE => to_date('23:11 01/06/06','HH24:MI DD/MM/YY'),
 18   INTERVAL => 'trunc(SYSDATE) +7');
 19   COMMIT;
 20   END;
 21  /

PL/SQL procedure successfully completed.

SQL> select * from dba_jobs_running;

no rows selected

SQL> 
SQL> 
SQL> select job from user_jobs;

       JOB                                                          30                                                          31                                                          SQL> execute dbms_job.run(31);
BEGIN dbms_job.run(31); END;

*
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 1 
SQL> show parameter job

job_queue_processes                  integer                          12                                                          SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_                                                         PREETI                         CONNECT                        NO  YES NO                                                                                                              
PREETI                         DBA                            NO  YES NO                                                                                                                       PREETI                         RESOURCE                       NO  YES NO                                                                                                                       SQL> select * from v$version;

BANNER                                                         ----------------------------------------------------------------  Oracle9i Release 9.2.0.6.0 - Production                                                   PL/SQL Release 9.2.0.6.0 - Production                                                   CORE    9.2.0.6.0    Production                                           TNS for 32-bit Windows: Version 9.2.0.6.0 - Production                                                   NLSRTL Version 9.2.0.6.0 - Production                                                   
SQL> conn sys/***@ndsp as sysdba
Connected.
SQL> execute  dbms_job.run(30);
BEGIN dbms_job.run(30); END;

*
ERROR at line 1:
ORA-23421: job number 30 is not a job in the job queue 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_IJOB", line 529 
ORA-06512: at "SYS.DBMS_JOB", line 258 
ORA-06512: at line 1 
SQL> spool off

 THis is user sys:

SQL> show parameter job

NAME                                 TYPE                             VALUE                                                                             
job_queue_processes                  integer 12                        
SQL> execute dbms_job.run(30);
BEGIN dbms_job.run(30); END;

*
ERROR at line 1:
ORA-23421: job number 30 is not a job in the job queue 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_IJOB", line 529 
ORA-06512: at "SYS.DBMS_JOB", line 258 
ORA-06512: at line 1 

SQL> Declare
  2  N number;
  3  BEGIN
  4   DBMS_JOB.SUBMIT(JOB => n,
  5   WHAT =>' BEGIN
  6  For x in  (select username  from sys.dba_users
  7  where username IN(SELECT DISTINCT(OWNER) FROM sys.DBA_OBJECTS) and  account_STATUS=''OPEN'')
  8  LOOP
  9  dbms_stats.gather_schema_stats(OWNNAME =>x.username,
 10  ESTIMATE_PERCENT => 20,
 11  METHOD_OPT =>''FOR ALL COLUMNS SIZE 75'',
 12  DEGREE => 1,
 13  CASCADE => TRUE);
 14  END LOOP;
 15  commit;
 16  END;',
 17   NEXT_DATE => to_date('23:11 01/06/06','HH24:MI DD/MM/YY'),
 18   INTERVAL => 'trunc(SYSDATE) +7');
 19   COMMIT;
 20   END;
 21  
 22  /

PL/SQL procedure successfully completed.
SQL> execute dbms_job.run(32);
PL/SQL procedure successfully completed.
SQL> spool off
 The follwing message in the alrt log file
Errors in file d:\oradata\ndsp\admin\udump\ndsp_ora_2800.trc:
ORA-12012: error on auto execute of job 31
ORA-06550: line 3, column 51:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 42:
PLS-00364: loop index variable 'X' use is invalid
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
 cant figure out why.... 

Hi Tom,
I read this thread and it seems that I still have a question:

Basic question is how do I change PRIV_USER before/after I submit the job? Submit_Job does not allow this.

In detail:

If I do following,

connect system/system
alter session set current_schema=scott

create table xyz
(a number);

it creates a table in SCott's schema.

If I do this:

connect system/system
alter session set current_schema=scott

execute dbms_job.submit(....);

it creates a job with log_user=priv_user='SYSTEM' and schema_user='SCOTT'. This obviously fails during execution due to missing objects in SYSTEM schema.

Question is, how do I write the script so that these all 3 values are set as SCOTT without connecting as scott? The reason we need this is to automate release scripts by means of logging as SYSTEM and then changing context of schema (we have aroudn 5 schema for our release) by means of issuing alter session set current_schema commands.

I hope I clarified my question. Thanks for your help in advance.

BTW, I started reading your new book; it rocks - as always! (i was pretty amazed after reading the pre-update trigger re-start secret!)

Thanks.

-Bipin.

I thought I am sort of connecting as scott when I used
ALTER SESSION SET CURRENT_SCHEMA=scott. Apparently it's not true. I realize my mistake now.

Can you please clarify what do you mean by 'sort of connect to scott'? So, basically what I am asking is how do I connect to scott without using "connect scott/tiger" in sqlplus?

Thaks, Tom.

-Bipin.

Hi Tom,
I have proc1 that calls proc2 using a "while 1=1" loop - causing it to execute repeatedly in a serial fashion.

If proc1 terminates unexpectedly, how would I restart it using proc2?

I've experimented with calling proc1 with a dbms_job but what type of code would I need in proc1 that would exec proc2 if it wasn't running anymore? I want to prevent parallel executions of proc2. I've experimented with queries to user_jobs, v$sqlarea, and v$session but am unable to come up with something that would do this in proc1:

...
select count(*) into is_running from xxx;
if is_running = 0 then
exec proc2;
end if;
...

I guess my question would be:
Is there a data dictionary table that would tell me that a stored proc is scheduled to run so don't fire off another one? Merely finding out if a proc is running now won't do the trick because I don't want multiple procs running in parallel and, if a sleep of say 6 hours is occurring, I wouldn't know how to prevent the proc firing off in the third hour.

tia

Tom,
Sorry for incorrectly communicating my design.

I would like job1 to run every hour. It calls proc1 which checks if proc2 is still scheduled to run or is running. proc2 calls multiple procs in a while loop that never terminates (except for sleeps of > than 1 hour).

In the event that proc2 is unexpectedly killed or terminates, is there a method to having proc1 call proc2 again?

Since proc2 isn't "continuously running" (it sleeps for a specified time), it doesn't seem I can query v$session/v$sqlarea/user_jobs.this_date (or another data dictionary table) to figure out when (if at all) to exec proc2 again.

Would just checking if job1 is "broken" be enough to ascertain that proc2 is in "good" shape?

That makes perfect sense. I'll create job1 to call proc2 with no next_date.
Thanks again.

Hi Tom,
I've implemented a job1 that calls proc1 which calls proc2.

Everything is going well but I'm hoping you can help me with these questions.

1. How do I prevent job1 from running in parallel? I've experimented with running dbms_job on a dbms_lock.sleep(10) command with no "next_date" and the job disappears after running so I don't have to worry about it being run again. However, what if the job runs continuously or for a period long enough for someone to execute it again and the job requires non-parallel execution?

2. I'm using the job1->proc1->proc2 technique to prevent someone with DBA privs from offlining the job. I know this isn't a failsafe method but wrapping the procs and naming it something that doesn't peak the interest of a DBA seems to be better than not. What data dictionary tables should I use to generate an email alert if:
a. job1 is offlined, removed, or executed
b. proc1/proc2 is killed, executed, run in parallel

I guess I'm going back to a failsafe method (using data dictionary tables) to insure that proc1 checks to see if proc2 is running (or scheduled to run) before executing.

Thanks again for your time.

Tom,

What INIT.ORA parameters do we need to set for scheduling jobs via DBMS_JOB in 9i(Like JOB_* i 8i)

Cheers,

Thanks Tom,

Did you mean I need to set only JOB_QUEUE_PROCESS parameter? As JOB_QUEUE_INTERVAL parameter is Obsolete in 9i. Did I get you right?

Sincerely,

Hi Tom
I have a snapshot and when i insert or update a record the "last_refresh" column in the "DBA_SNAPSHOT" always is in 01-01-1950, and yhe next refresh date change to 01-01-4000 after a snapshot refresh.
have you any idea how to overcome this problem?

How do i enter a job to be executed on the first Sunday of every month?

next_day(last_day(sysdate),'SUN')

Tom,
If i pass database link name as 'dbprod18', i want to assign the value to upper case in dbms_job.submit job.

How to do that ?. I tried below.

=====
declare
n number ;
l_REMOTEDBLINK varchar2(30) ;
begin
select UPPER('&REMOTEDBLINK') into l_REMOTEDBLINK from dual ;
dbms_job.submit( n, 'refresh_pkg.mv_refresh(p_db_link => l_REMOTEDBLINK);',
TRUNC(sysdate)+6/24,
'TRUNC(sysdate)+1+6/24' );
end;


I want the output as :

refresh_pkg.mv_refresh( p_db_link => 'DBPROD18') in dbms_job.

===

ops$tkyte%ORA10GR2> create table debug_table( msg varchar2(80) );

Table created.

ops$tkyte%ORA10GR2> create or replace package refresh_pkg
  2  as
  3    procedure mv_refresh( p_db_link in varchar2 );
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2> show errors
No errors.
ops$tkyte%ORA10GR2> create or replace package body refresh_pkg
  2  as
  3    procedure mv_refresh( p_db_link in varchar2 )
  4    is
  5    begin
  6          insert into debug_table values ( 'was passed ' || p_db_link );
  7    end;
  8  end;
  9  /

Package body created.

ops$tkyte%ORA10GR2> show errors
No errors.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table job_parms
  2  ( job_id number primary key,
  3    p_db_link varchar2(30)
  4  ) organization index;

Table created.

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2> declare
  2          l_job number;
  3          l_what long := '
  4  declare
  5          l_inputs job_parms%rowtype;
  6  begin
  7          select * into l_inputs from job_parms where job_id = JOB;
  8          refresh_pkg.mv_refresh( p_db_link => l_inputs.p_db_link );
  9          delete from job_parms where job_id = JOB;
 10          commit;
 11  end;';
 12
 13  begin
 14          dbms_job.submit( l_job, l_what );
 15          insert into job_parms values ( l_job, 'hello there' );
 16          commit;
 17  end;
 18  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from debug_table;

no rows selected

ops$tkyte%ORA10GR2> exec dbms_lock.sleep( 5 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from debug_table;

MSG
-------------------------------------------------------------------------------
was passed hello there

Ok, dumb question. I thought PL/SQL didn't require bind variables. And PL/SQL begins when a BEGIN and END statement exist, correct?

Gotcha, makes sense now.

Thanks Tom for the bind variable friendly approach.

In my case, my database link will be the same for that sp call for that database.

refresh_pkg.mv_refresh( p_db_link => 'DBPROD18') ;

a) In this case, Will it be soft parsed after first time hard-parse (same input value).

b) Can you show us how to do this with simple dbms_job.submit .

While submitting dbms_job, i want to accept the input (database link name) and pass it in upper case to dbms_job.submit (sp call). From there on, dbms_job will just schedule that job.

I am looking for the output as :

refresh_pkg.mv_refresh( p_db_link => 'DBPROD18') in dbms_job.

It works great. Thanks Tom.

Tom,

I executed the sp 3 times from sqlplus with trace enabled.

My trace shows , parse 3, execute 3 .

But i am using the same sp with the same exact input.

am i missing anything ? Thanks

SQL> alter session set sql_trace=true ;

Session altered.

SQL> exec networx_mv_refresh_pkg.networx_mv_refresh( 'DBST03' );

PL/SQL procedure successfully completed.

SQL> exec networx_mv_refresh_pkg.networx_mv_refresh( 'DBST03' );

PL/SQL procedure successfully completed.

SQL> exec networx_mv_refresh_pkg.networx_mv_refresh( 'DBST03' );

PL/SQL procedure successfully completed.


Trace output :
********************************************************************************

BEGIN networx_mv_refresh_pkg.networx_mv_refresh( 'DBST03' ); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.02 0.03 0 54 0 0
Execute 3 1.00 1.08 0 241 209 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 1.02 1.11 0 295 209 3

Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 57
********************************************************************************

Hi tom,

We are calling a procedure under dbms_job and it is executing at defined time.

However in the V$session for that session I am not able to get the prev_sql_id and sql_id information which are there in the PL/SQL procedure. They are null entries.

However when I execute the proc from SQL* prompt the prev_sql_id and sql_id entries are displayed.

What is the reasoning behind this? Why the proc running under dbms_job doesn't populate the prev_sql_id and sql_id entries for the procedure under execution.

Many thanks

sir,
I have created four procedure and bind it to four different jobs that will run after 10 minute.
But in job queue all the job starts serially means one-by-one,
and it takes more time. I want to execute four-jobs at a time.
What should I do? I am using oracle-9i.

Hi Tom,
Is it possible to run a job everyday from 10.15AM to 12.15PM at 10 minutes interval. Actually I need to update some data in a table in that interval. The job should run at 10.15 AM, 10.25 AM, 10.35 AM and so on till 12-15 PM everyday. It would be great if you send the code to do this. Thanks a lot.

Thanks a lot Tom for your prompt response. I have done that. Thanks again.

Tom:

I want to check the best way to do this:

let us say whenever you add a record to EMPLOYEE table you insert a "handling_date" and on that date a procedure PROCESS_EMPLOYEE needs to be run.

So I do an after insert trigger (or in save_employee) procedure that schedule the job PROCESS_EMPLOYEE to run on HANDLING_DATE.

I save a record into my audit_trail table
EMPLOYEE_PROCESS_JOBS and save any parameters requried for the job here.

Now, Let us say the HANDLING_DATE can be updated. Do you save the JOB_ID into the EMPLOYEE table and read it from tehre and kill the old job and schedule a new one with a new date OR you leave both jobs and you code it in such a way that if process runs it will check the DATE in EMPLOYEE_PROCESS_JOBS against HANDLING_DATE in EMPLOYEE table and will not do anything if they do not match.

Basically, you just leave the first job run and do nothing.

Tom:

Another small confirmation with you.

When an application receives a status message about a book test status I insert a record into BOOK_TEST log table. I also insert a record into EMAIL_LOG table for the email information that needs to be sent out to vendor.

The question, I want to have the email sent via a job. Would you just add another column to the EMAIL_LOG (JOB_ID) and have SEND_EMAIL(JOB) read it from there or you create another table for EMAIL_PARAMETERS. I do not see any need for a second table. do you?

Tom:

I noticed in the way you do emails, or abtch jobs you always create a second table like "Send_mail_data" in your book.

Why do not you just add a new column (JOB_ID) to the ORDERS or the transaction table (job_id) that requries an email to be sent and let the job get the email address information from there instead of creating a second table (SEND_MAIL_DATA) and storing it again there.

Ah, OK, very convincing, You are creating a generic email table for everything that needs an email sent.

a question on your comment:

<or you could just change the date the job is to run, you can do that too. Sure, consider the job_id a foreign key to the jobs information and maintain that data like you would any other data. >

If the job is not for sending emails, but for processing customer orders on process date the End of each month.
ORDER_DATA and ORDERS table are populated on beginning of month for customers and customer only fills out the ORD_QTY column before process_Date.

Here is how tables look like. not very good but that is what is there now.

ORDER_DATA (PARENT)
------------
order_date <PK>
Order_Status
create_date

....

ORDERS (CHILD)
------------
order_date <PK>
cust_id <PK>
part_no <PK>
process_date
ord_Qty
create_date
....

I want to schedule ONE job after populating the ORDERS table for all customers and would run on the process_date.


what would you do:
a. Would you add a column to ORDER_DATA,JOB_ID and then schedule the job that pulls up the info from the record.
b. Create an ORDER_JOB_LOG with basic info and the job will look up info from this table.
c. Do a and B. Have the column in a be a foreign key to b.

Option a

ORDER_DATA
-----------
order_id
....
job_id

Option b

ORDER_JOB_LOG
----------------
job_id
order_id
processing_Date
created_date



2. Do you recommend just changing the date of the job on update of processing date? I guess i have to do that in the log table and job oracle scheduler. right.

How do you do it in oracle for scheduler?

Tom:
you do not have any recommendation on the above so i can finalize my design in the best way.

Tom:

I am more inclined to add "Job_id" to the ORDER_DATA table and not create a new table.

However, do you think this will work based on that design

The client app (not oracle) will insert 1 record into ORDER_DATA and 10,000 records (200 records per customer and 50 customers) into ORDERS.

ORDER_DATA (PARENT)
------------
order_date <PK>
Order_Status
create_date
job_id

....

ORDERS (CHILD)
------------
order_date <PK>
cust_id <PK>
part_no <PK>
process_date
ord_Qty
create_date

An AFTER INSERT statement level trigger defined on ORDERS will be triggered that will schedule the job "process_order"
on "process_date" and then update the ORDER_DATA.job_id with the job number.

when job runs on "process_date" it will read ORDERS_DATA.ORDER_DATE and then ORDERS.PROCESS_DATE and process all the records in ORDERS table.

If application updates ORDERS.PROCESS_DATE for that ORDER_DATE (before 1st job runs), then application (oracle here) will read the job number from ORDER_DATA, delete the job and schedule a new one and update the table again with new job number.

Do you see any potential problems technically in that.

I have a bit of a problem with the Oracle Queue Scheduler in Performance Test (please excuse me if I have not called it the correct name). I'm talking about the queue that you can submit jobs to (with or without a given time or interval) and Oracle will process these jobs in due course.

I used the following code to submit a job:
declare
p_job integer;
begin
dbms_job.submit( p_job,'begin'||chr(10)||
'insert into test_job_queue values(2,''test number two'');'||chr(10)||
'end;',
sysdate);
commit;
end;
/
Normally, a job submitted this way would process immediately in the background. Indeed, I have tried exactly the same code in the Development environment and it works normally. I can see the job was successfully issued to the queue by checking view user_jobs:

select JOB from user_jobs;
372283
Then I have to execute manually.
exec dbms_job.run(372282);

Do you know what is wrong with queue? Is this something you can help with?

Regards,
Jeevan

Hi,

Job queue is already set to 15 but still we are facing problem.

NAME TYPE VALUE
job_queue_processes integer 15

why it is not processing/executing the job which are placed in the queue automatically after executing a procedure.
we need to execute manually each time when a procedure is ran,
select JOB from user_jobs;
372283
exec dbms_job.run(372282);

Regards,
Jeevan

Hi,

The problem is that when we submit jobs into ORACLE using dbms_job they are not being executed. Before xxx was taken down for a backup this was working fine, however when the database was brought back up the ORACLE queue is no longer being processed which means that I have to run each job individually using dbms_job.run.
Everything is fine regarding constraints but still i don't understand why it is not picking a job soon after we submit a procedure/job. usually jobs would process immediately in the background.
I forgot to tell...I am using 9i and I found the hint that the JOB_QUEUE_INTERVALL isn't supported.

Regards,
Jeevan

Tom:

I have an ORDERED_ITEMS table and I want to schedule a job to process all orders
at the end of each month.

ORDER (PARENT)
------------
order_date <PK>
Order_Status
create_date

ORDERED_ITEMS (CHILD)
------------
order_date <PK>
cust_id <PK>
part_no <PK>
order_Qty
month_process_date
create_date

We have about 100 customers. We add one record to ORDER table each month for all of them with the first day of the month as the PK.

All the orders added for all customers in a particular month have the same "month_process_date".

A system admin can update the "month_process_date" for all the orders if status is "Open".

Do you see any potential issues by scheduling a job that runs every day and checks whether the "month_process_date" is the same as sysdate and then execute instead of scheduling the job once only on the "month_process_date"?


begin

select max(order_date) into v_order_date from order where order_status = 'Open';
select distinct month_process_date
into v_month_process_Date from ordered_items where order_Date = v_order_Date;

IF to_char(v_month_process_date+1,'DD-MON-YYYY') = to_char(sysdate,'DD-MON-YYYY') THEN process_orders(v_order_date,job);
END IF;

END;

Thanks,

Tom:

I did not create the db/table. yes it is in the wrong place.

the goal here is not for sys admin to schedule the job.
it is automatically scheduled. However, he just have a menu where he can extend that date if he decides to.

so the option that i am asking you about whether is it problematic to schedule a job that runs every day and keeps checking "do i need to run that end of month process today?"

is it in any way better to have that job scheduled on that date on end of month only instead of every day.

or both options are valid.

Well fixing the design requires a lot of paperwork and signatures and things will be so delayed. I think you are suggesting to move "process_date" to the parent table "order" where it really belongs.

Not sure about your point on "unextend" the date. let say you changed the date from june 30 to june 25.

As long as the new date is less than sysdate, then the daily job will pick it up. If application blocks them from changing date to earlier than sysdate then we are OK.

However, option #2 if you schedule one job per month, then you can reshedule it to the prior date. Same rule applies as above if they changed it to a date less than sysdate then it will run the job immediately.


Are you saying you do not want to have your name on the change history because of the bad DB design or is because the way job is scheduled.

How would you schedule the job your way? This is why I always like to implement.

ORDER  (PARENT)
------------
order_date  <PK>
Order_Status
create_date
job_id     <b> <fk in fact to user_jobs, the month end job can update the other fields in this row when it runs></b>
date_job_actually_run
outcome_of_job_actually_running
time_job_took_to_run
other_interesting_historical_facts...


ORDERED_ITEMS (CHILD)
------------
order_date <PK>
cust_id    <PK>
part_no    <PK>
order_Qty 
create_date

That is Excellent. thanks tom.

hi Tom,

my job in SQL:
begin
sys.dbms_job.change(job => :job, what => :what, next_date => :next_date, interval => :interval);
end;


:JOB=104
:WHAT=pc_inscr_seman_porvenir('inscritos_porv.txt','/datos/rbs');
:NEXT_DATE=30/07/2008 06:00:00 a.m.
:INTERVAL=TRUNC(SYSDATE+1)

If i execute my Job, this created my inscritos_porv.txt.... ok
But the Nex Execution don´t created....

=(

Tom,

I get this error ORA-01858 but only when I schedule job.
If I run procedure from command prompt it works just fine. 
I am running 10.2.0.4 on RH Linux. Any idea what causes this error?

Thanks in advance

Here is the setup.

create table t as select * from all_objects;

create table t_summary (day date, object_name varchar2(30), count number);

 create or replace procedure p as
    begin
       insert into t_summary
          select sysdate day, object_name, count(*) count
            from t
           group by sysdate, object_name;
    end;

variable n number
 begin
          dbms_job.submit (job => :n,
      what => 'p',
      next_date => 'SYSDATE+5/1440',
      interval  => 'SYSDATE+5/1440');
   commit;
 end;
SQL> /
        dbms_job.submit (job => :n,
                   *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected 
ORA-06512: at line 2 

PROCEDURE SUBMIT
 Argument Name                  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

next_date => 'SYSDATE+5/1440',

Sorry tried that too different error.

variable n number
begin
dbms_job.submit (job => :n,
what => 'p',
next_date => SYSDATE+5/1440,
interval => 'SYSDATE+5/1440');
commit;
end;
/
begin
*
ERROR at line 1:
ORA-06550: line 1, column 95:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 2

Try putting a semicolon ";" in your what.
e.g.
variable n  number;
begin
dbms_job.submit(job     => :n,
                what    => 'p',
                next_date => sysdate+5/1440,
                interval  => 'sysdate+5/1440');
commit;
end;
/

SQL> /
begin
*
ERROR at line 1:
ORA-06550: line 1, column 95:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at line 2

variable n  number;
begin
dbms_job.submit(job     => :n,
                what    => 'p;',
                next_date => sysdate+5/1440,
                interval  => 'sysdate+5/1440');
commit;
end;
/

SQL> /

PL/SQL procedure successfully completed.
SQL> select what, next_date from user_jobs;
WHAT                 NEXT_DATE
-------------------- -------------------

p;                   2008/11/19 16:55:20


Seems to work for me

Hello Tom,

I want to create a DBMS_JOB that will execute twice per hour. It will run 5 minutes past the hour and half hour.

This job must execute on '10/12/2008 04:05:00' , '10/12/2008 04:35:00' , '10/12/2008 05:05:00',
'10/12/2008 05:35:00' .. and so on..

Please advice me how should I modify the following script to implement this logic:-


DECLARE

x NUMBER;

BEGIN

SYS.DBMS_JOB.submit (job => x,
what => 'CHECK_DIRECTORY_SPACE;',
next_date => TO_DATE
('10/12/2008 04:05:00','dd/mm/yyyy hh24:mi:ss'),
INTERVAL => 'SYSDATE + 1/48',
no_parse => FALSE);

SYS.DBMS_OUTPUT.put_line ('Job Number is: ' || TO_CHAR(x));
END;
/

Thanks and Best Regards
Hemal Deshmukh

ops$tkyte%ORA10GR2> select sysdate,
  2         trunc(sysdate,'hh') +
  3         case
  4             when to_number(to_char(sysdate,'mi')) between 0 and 4 then 5*1/24/60
  5             when to_number(to_char(sysdate,'mi')) between 5 and 34 then 35*1/24/60
  6             else                                                        65*1/24/60
  7             end
  8    from dual;

SYSDATE              TRUNC(SYSDATE,'HH')+
-------------------- --------------------
09-dec-2008 12:44:42 09-dec-2008 13:05:00

ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          dbms_job.submit
  3          ( job => :x, what => 'null;', next_date => sysdate,
  4             interval =>
  5             'trunc(sysdate,''hh'') + case
  6             when to_number(to_char(sysdate,''mi'')) between 0 and 4 then 5*1/24/60
  7             when to_number(to_char(sysdate,''mi'')) between 5 and 34 then 35*1/24/60
  8             else 65*1/24/60 end' );
  9  end;
 10  /

PL/SQL procedure successfully completed.

CREATE OR REPLACE VIEW system.re_user_jobs_10g_upgrade (
state_of_current_job,
submit_sql,
broken_state,
job_owner )
AS
with jobs_doc as (
select 'DOC' state_of_current_job,
'The arguments for dbms_job.submit include two time-related parameters, next_date and interval.'||chr(10)||
' next_date (date) is the next date you want the job to run '||chr(10)||
' interval (varchar2) expression that evaluates to a date. Each time the job is run,'||chr(10)||
' interval reevaluated result set as the next_date the job will run.'||chr(10)||
' ' submit_Sql, '?' broken_state, null job_owner
from dual
union all
select 'N' state_of_current_job,
'declare'||chr(10)||
' my_job number;'||chr(10)||
'begin'||chr(10)||
' /* prev instance='||to_char(instance)||' job#='||job||' last_date ='||chr(39)||to_Char(last_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||chr(10)||
' * next_date ='||chr(39)||to_Char(last_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||chr(10)||
' ************/'||chr(10)||
' dbms_job.submit(job => my_job,'||chr(10)||
' what => '||chr(39)||schema_user||'.'||what||chr(39)||','||chr(10)||
' next_date => to_date('||chr(39)||to_char(next_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||','||chr(39)||'dd-mon-yyyy hh24:mi:ss'||chr(39)||'),'||chr(10)||
' interval => '||chr(39)||interval||chr(39)||'); '||chr(10)||
' commit;'||chr(10)||
' dbms_job.broken(job=>my_job,broken=FALSE);'||chr(10)||
'end;'||chr(10)||
'/'||chr(10)||' '
submit_Sql, 'N' broken_state, schema_user job_owner
from dba_jobs where broken = 'N'
union all
select 'Y' state_of_current_job,
'declare'||chr(10)||
' my_job number;'||chr(10)||
'begin'||chr(10)||
' /* prev instance='||to_char(instance)||' job#='||job||' last_date ='||chr(39)||to_Char(last_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||chr(10)||
' * next_date ='||chr(39)||to_Char(last_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||chr(10)||
' ************/'||chr(10)||
' dbms_job.submit(job => my_job,'||chr(10)||
' what => '||chr(39)||schema_user||'.'||what||chr(39)||','||chr(10)||
' next_date => to_date('||chr(39)||to_char(next_date,'dd-mon-yyyy hh24:mi:ss')||chr(39)||','||chr(39)||'dd-mon-yyyy hh24:mi:ss'||chr(39)||'),'||chr(10)||
' interval => '||chr(39)||interval||chr(39)||'); '||chr(10)||
' commit;'||chr(10)||
' dbms_job.broken(job=>my_job,broken=TRUE);'||chr(10)||
'end;'||chr(10)||
'/'||chr(10)||' '
submit_Sql , 'Y' broken_state, schema_user job_owner
from dba_jobs where broken = 'Y'
) select "STATE_OF_CURRENT_JOB","SUBMIT_SQL","BROKEN_STATE","JOB_OWNER" from jobs_doc order by
decode(broken_state,'Y',1,'N',2,3)
/

Hi Tom,

I have submitted this job, which is sheduled to run at 01.00 Am on every day. But if you look at the NEXT_DATE, it is earlier than, the THIS_DATE.
Am I missing some thing.?

Johny Alex

set serveroutput on
VARIABLE jobno NUMBER
declare
jobcount number := 0;
begin
select count(*)
into jobcount
from user_jobs t
where t.what = 'sp_raise_swift_standinstr_evnt;';

if jobcount >= 1
then
dbms_output.put_line('sp_raise_swift_standinstr_evnt, Please stop sp_raise_swift_standinstr_evnt before starting again');
else
DBMS_JOB.SUBMIT(job =>:jobno,
what => 'sp_raise_swift_standinstr_evnt;',
next_date => sysdate,
interval =>'trunc(sysdate+1) + 1/24');
commit;
end if;
end;
/

exit



JOB 444
LOG_USER DMGD28
PRIV_USER DMGD28
SCHEMA_USER DMGD28
LAST_DATE < EMPTY STRING >
LAST_SEC < EMPTY STRING >
THIS_DATE 25.02.2009 10:56:53
THIS_SEC 10:56:53
NEXT_DATE 25.02.2009 10:56:52
NEXT_SEC 10:56:52
TOTAL_TIME 98
BROKEN N
INTERVAL trunc(sysdate+1) + 1/24
FAILURES < EMPTY STRING >
WHAT sp_raise_swift_standinstr_evnt;
NLS_ENV NLS_LANGUAGE='AMERICAN'.....
MISC_ENV 0102000200000000
INSTANCE 0

Sorry Tom,

I am totally confused.

How do I change the code, so that It will run at every 01.00?

I will be submitting this job sometime today.

Thanks in advance
Johny

Hi Tom,

Bind Variables,as you always say, are very important for scalabality and here, I am trying to fix a code that submits a job almost 1000 times using literals. How do I make use of bind here. I tried with different methods, but get an error :

ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "SCOTT.TEST_BIND", line 29
ORA-06512: at line 1

The code I used is as under :
The 1st assignment of v_proc is with literals, which was the original code.
The 2nd assignment, I am trying to make use of bind variables.


CREATE OR REPLACE PROCEDURE Test_bind(v_circle_id VARCHAR2) IS
tmpVar NUMBER;
v_proc VARCHAR2(100);
v_date DATE;
v_job NUMBER;
ljob number;
cursor cu_proc_ranges is
select circle_id, start_code, end_code from test_vivek
where circle_id=v_circle_id;
i_cid varchar2(4);
i_sc varchar2(5);
i_ec varchar2(5);
BEGIN
tmpVar := 0;

v_date:=sysdate;
FOR i IN cu_proc_ranges LOOP
v_proc:= 'TEST_BIND_JOB('||CHR(39)||i.circle_id||CHR(39)||','||chr(39)||i.start_code||chr(39)||','||chr(39)||i.end_code||chr(39)||');';
dbms_output.put_line(v_proc);
dbms_job.submit(v_job,v_proc,v_date);
commit;
v_date:=v_date+5/(24*60*60);
i_cid:=i.circle_id;
i_sc:=i.start_code;
i_ec:=i.end_code;
v_proc:=q'[declare ljob number; begin dbms_job.submit(ljob,'test_bind_job(:1,:2,:3);',:4); end;]';
dbms_output.put_line(v_proc);
execute immediate v_proc using i_cid, i_sc, i_ec, v_date;
-- execute immediate 'begin dbms_job.submit(what=>''test_bind_job(:b1,:b2,:b3);'',:d); end;' using i_cid, i_sc, i_ec, v_date;
-- commit;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
-- WHEN OTHERS THEN
-- RAISE;

END test_bind;
/

Would appreciate your help.
Thanks

i am submitting the job, but after submitting the job i couldnt able to see in dba_jobs as well as dba_jobs_running tables.
1. with out commit i can able to see in dba_jobs table
2. once i commit i couldnt able to see

Please help me out what is the cause for this

declare
l_what VARCHAR2(1000);
l_job BINARY_INTEGER;
BEGIN

l_what := 'BEGIN ifpks_utility.pr_send_msg; END;';

dbms_job.submit(l_job,l_what);

DBMS_OUTPUT.PUT_LINE('Job is submitted'||l_what||'~'||l_job);

COMMIT;
end;

hi,Tom
when i use the JOB in my package,then submit it.
dbms_job.submit(l_job_number,
'launch_child(JOB);',SYSDATE,
'sysdate+1');
but in the table all_jobs,

SELECT what
FROM all_jobs
WHERE job>170
ORDER BY job;
----------------
launch_child(JOB);
launch_child(JOB);
launch_child(JOB);

so the job don't run.how can i slove it,can you help me.
where does the variable "JOB" from, which is it documented ?
Thanks

DECLARE 
   job BINARY_INTEGER := :job; 
   next_date DATE := :mydate;  
   broken BOOLEAN := FALSE; 
BEGIN<b>
         WHAT</b>
    :mydate := next_date; 
    IF broken THEN :b := 1; ELSE :b := 0; END IF; 
END;

Thanks Tom for useful information. But I have quick question.

Is there a way to get the JOB Original creation date in Oracle 9i?
also I have a Interval 1 does it mean its One day Interval?
Here a JOB details:
APPS 23756 N 15 31-JAN-10:00:09:08 01-FEB-10:00:09:08 1 execute immediate 'grant SELECT on JEG.JEG_HR_UKIRE_SALBOOK_BENEFIT to APPS_QUERY_ROLE';

Tom,

I have to run a procedure hourly and within that hour the proc witll run 5 times with an interval of 5 seconds.It means in first 25 seconds the procedure will run 5 times.
This cycle will run again after 1 hour interval.

I tried dbms_lock.sleep(5) inside the procedure but after submiiting the job which should run after 1 hr didn't work . This job is not running may be dbms_lock.sleep() can't be used inside Job Definition.

This is the first time I am posting the question your help is really appreciated.

Thank you Tom After reading your book and doing some R&D I have resolved the problem . After looking into alert log I came to know there was no grant to execute db_lock package .

Hi,

We have one user called ops$batch and another user called ops$job. All BG jobs run under this user ( log_user is ops$job ). Now I would like to submit oracle BG job from ops$batch in such a way that it submits under ops$job or in other words , log_user for that job submitted from ops$batch should be ops$jobs. Please let me know how we can do this.

Thanks
Adam

Tom:

I want to schedule a job to run run_my_web_proc procedure every quarter or 4 times per year: 3/31/2011, 6/30/2011, 9/30/2011, 12/31/2011, 3/31/2012, etc.. at 8:00 PM.

How would you do it?

Would you use decode to check the month it runs and then decide to add 90 days or 91 days.

ops$tkyte%ORA11GR2> select last_day(add_months(trunc(dt,'mm'),2))
  2    from (select to_date( '01-apr-2011') dt from dual )
  3  /

LAST_DAY(
---------
30-JUN-11

Tom:

Actually you brought up a good point. This HTML report is based on calculated number derived via nother job that runs every morning at 4:00 AM.

The first job does not take that more than 5 minutes. This archival one should also run quick. But that means I would need to move that first job to run at 7:00 PM before this one that save the webpage with refreshed numbers.

If job files I need to send myself an email so I know and run it manually because this is a quarterly report. I need to ahve a snaphot at end of quarter. NExt day things change.


Does this sound correct for you to schedule it at 8:00 PM end of each quarter. The 2nd one is the first day of next quarter. I plugged in the DATES to see what the system will do using SYSDATE for interval when it runs.

1  select last_day(trunc(sysdate))+20/24,
  2         last_day(add_months(trunc(to_date('31-MAR-2011'),'mm'),3))+20/24,
  3         last_day(add_months(trunc(to_date('30-JUN-2011'),'mm'),3))+20/24,
  4         last_day(add_months(trunc(to_date('30-SEP-2011'),'mm'),3))+20/24
  5*  from dual
SQL> /

LAST_DAY(TRUNC(SYSDA LAST_DAY(ADD_MONTHS( LAST_DAY(ADD_MONTHS( LAST_DAY(ADD_MONTHS(
-------------------- -------------------- -------------------- --------------------
31-mar-2011 20:00:00 30-jun-2011 20:00:00 30-sep-2011 20:00:00 31-dec-2011 20:00:00

1 row selected.



  1  select last_day(trunc(sysdate))+20/24,
  2         last_day(add_months(trunc(to_date('01-APR-2011'),'mm'),2))+29/24,
  3         last_day(add_months(trunc(to_date('01-JUL-2011'),'mm'),2))+29/24,
  4         last_day(add_months(trunc(to_date('01-OCT-2011'),'mm'),2))+29/24
  5*  from dual
SQL  > /

LAST_DAY(TRUNC(SYSDA LAST_DAY(ADD_MONTHS( LAST_DAY(ADD_MONTHS(
-------------------- -------------------- -------------------- LAST_DAY(ADD_MONTHS(
--------------------
01-apr-2011 05:00:00 01-jul-2011 05:00:00 01-oct-2011 05:00:00
01-jan-2012 05:00:00

Tom:

<<and what happens when you quit, get fired or die? >>

I do not understand what you mean. Let us forget I will troubleshoot and try it manually.

What do you really expect me to do in case oracle has some problem that day. There are planes that fail every day and crash too. When you drive home you are taking a risk someone drunk or lunatic will hit you. What do you do?

I cant run the 2 jobs together. One job calculates inventory statistics every night for the HTML report.

The other job which archives the report/webpage needs only to run at the end of the quarter (4 times per year).

Tom:

Thanks for the excellent advise. I will implement it per your advice. There are very few people on planet who think like you.

But 2 quick questions:

1) Do you know why I keep getting an error on this job schedule due to 'mm' in interval?


SQL> exec dbms_job.submit(:l_job,'STOCK_REPORTS.RUN_ARCHIVE_REPORT',trunc(sysdate)+22/24,'last_day(add_months(trunc(sysdate,'mm'),3))+22/24')
BEGIN dbms_job.submit(:l_job,'STOCK_REPORTS.RUN_ARCHIVE_REPORT',trunc(sysdate)+22/24,'last_day(add_months(trunc(sysdate,'mm'),3))+22/24'); END;

                                                                                                                             * ERROR at line 1:
ORA-06550: line 1, column 126:
PLS-00103: Encountered the symbol "MM" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol ". was inserted before "MM" to continue.


2) Is this a good way to implement email alerts for the page dump?

PROCEDURE RUN_ARCHIVE_REPORT
  AS
    l_job                 number;
   BEGIN
     stock_reports.pretened_this_is_web_procedure('mike');
     stock_reports.dump_report;
     dbms_job.submit(l_job,'PKG_EMAILS.SEND_EMAIL_PASS(JOB);');
   EXCEPTION
    WHEN OTHERS THEN
      dbms_job.submit(l_job,'PKG_EMAILS.SEND_EMAIL_FAIL(JOB);');
   END;

ops$tkyte%ORA11GR2> select 'hello ''mm'' world' from dual;

'HELLO''MM''WORL
----------------
hello 'mm' world

ops$tkyte%ORA11GR2> select q'|hello 'mm' world|' from dual;

Q'|HELLO'MM'WORL
----------------
hello 'mm' world

Tom:

I should have told you before i tried the double quotes and it did not work.

SQL> exec dbms_job.submit(:l_job,'STOCK_REPORTS.RUN_ARCHIVE_REPORT',trunc(sysdate)+22/24,'last_day(add_months(trunc(sysdate,''mm''),3))+22/24')
BEGIN dbms_job.submit(:l_job,'STOCK_REPORTS.RUN_ARCHIVE_REPORT',trunc(sysdate)+22/24,'last_day(add_months(trunc(sysdate,''mm''),3))+22/24'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 130:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 1


So you really think there are a *lot* of people out there that think like you? I would like to see your list of those people. 

Tom:

yes you are right. I had a semi colon before and i took it out and forgot to put it back.

If you are rating people by position title then sure there are ton of these around. However, I think most are incompetent. It is common trend now. 50% of IT projects end up with failures for a reason. Just look at those top people running fannie Mae, Freddie Mac, Sallie Mae, AIG, Lehman, etc, etc. They are all bankrupt.

IMO, the IT industry is *filled* with incompetent people these days. Needs heavy flushing.

How do you normally track job runs? Do you log it in an application table or do you send email alerts as part of the email, or you just monitor the DBA_JOBS view? would doing something like this in every job I want to track is good.

PROCEDURE RUN_ARCHIVE_REPORT
AS
l_job number;
BEGIN
stock_reports.pretened_this_is_web_procedure('mike');
stock_reports.dump_report;
dbms_job.submit(l_job,'PKG_EMAILS.SEND_EMAIL_PASS(JOB);');
EXCEPTION
WHEN OTHERS THEN
dbms_job.submit(l_job,'PKG_EMAILS.SEND_EMAIL_FAIL(JOB);');
END;

procedure ...
is
   ...
begin
    ....
exception
when others then
    log_error_in_table_with_autonomous_transaction();
    RAISE;
end;

Sam writes:

>> IMO, the IT industry is *filled* with incompetent people these days. Needs heavy flushing.

Perhaps, but one is left to wonder what criteria should be used to sort the incompetent from the competent.

Tom:

Can you explain this?

I have about 5 jobs that I run at 3:00 AM. everything runs fine.

Now i have to run it at 9:00 PM. I keep getting job failures. I check the logs and i see some errors due to interval. When i ran it next day morning it runs fine.

It seems it does not like the interval and i am trying to schedule it at 9:00 PM every day.

Is there a bug with this 9202 job scheduler or do i need to add a few minutes or what?




Wed Mar 16 21:00:04 2011
Errors in file /ora920/admin/tips/bdump/tips_j001_10510598.trc:
ORA-12012: error on auto execute of job 406
ORA-12005: may not schedule automatic refresh for times in the past Wed Mar 16 21:00:06 2011 Errors in file /ora920/admin/tips/bdump/tips_j000_5783824.trc:



variable l_job number
/
exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,'trunc(sysdate)+21/24')
/

case when to_char(sysdate,'hh24') between '21' and '24' 
     then trunc(sysdate+1) 
     else trunc(sysdate) 
 end + 21/24

Tom:

why would always run fine at 3:00 AM though? I had an additional 1 in that interval though

variable l_job number
/
exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+1+3/24,'trunc(sysdate)+1+3/24')
/

Why do i need a case statement to check the hour? This statement should always work for 24 hour intervals. When it runs at 9:00 PM tonight, the interval will be firday night at 9:00 pm. correct?

variable l_job number
/
exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,'trunc(sysdate)+1+21/24')
/

Tom:

Ok, I see now your logic. it makes sense now.
I did not realize that I can schedule something at 9:00 pm and job scheduler may run it at 5:00 AM next day.

I am not sure about the circumstance that would cause that (i.e 1000 jobs in queue, job scheduler napping, etc., job failing) and the probability job run time will slip.
It does not sound very good if your jobs need to be serialized too (i.e job1 must run before job2)


I will do all my job intervals with the case statement. You taught me something new today.

BTW, it seems we see diff things when we close our eyes sometimes. Did you do a lot of yoga or tai-chi that improved your focus and make you visualize things different than most people.

Tom:

Are you sure you can use case in job interval?

I tried all kinds of things: no quote, single quote, double quote, parenthesis, etc. and it wont work.

 1   exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,
  2      'case when to_char(sysdate,''hh24'') between ''21'' and ''24''
  3        then trunc(sysdate+1)
  4        else trunc(sysdate)
  5*   end + 21/24'
SQL> /
 exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,
 *
ERROR at line 1:
ORA-00900: invalid SQL statement

ops$tkyte%ORA11GR2> variable l_job number
ops$tkyte%ORA11GR2> get test
  1  exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,
  2      'case when to_char(sysdate,''hh24'') between ''21'' and ''24''
  3      then trunc(sysdate+1)
  4      else trunc(sysdate)
  5*     end + 21/24'
ops$tkyte%ORA11GR2> /
exec dbms_job.submit(:l_job,'REPORT_LOAD(job);',trunc(sysdate)+21/24,
*
ERROR at line 1:
ORA-00900: invalid SQL statement

ops$tkyte%ORA11GR2> get test
  1  begin
  2  dbms_job.submit(:l_job,'null;',trunc(sysdate)+21/24,
  3      'case when to_char(sysdate,''hh24'') between ''21'' and ''24''
  4      then trunc(sysdate+1)
  5      else trunc(sysdate)
  6      end + 21/24' );
  7* end;
ops$tkyte%ORA11GR2> /

PL/SQL procedure successfully completed.

Tom:
Thanks, it is working now.

but is not slash and semicolon the same thing to end a sql statement

commit;
or
commit
/
same thing

I was using the same format that you provided on this page above

Format 1
----------------
variable n number;
exec dbms_job.submit( :n, 'null;' );
commit;

Format 2
-----------------------

variable n number;

begin
dbms_job.submit....
end;
/
commit;

ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> !cat test.sql
exec null;

ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> exec null;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> get test
  1* exec null;
ops$tkyte%ORA11GR2> /
exec null;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

I have a trigger in database A that needs to schedule a remote job in another database.

Is this possible? If not, can I schedule the job locally so it can call the remote package and pass the two parameters?



dbms_job.submit(l_job,'PKG_EMAILS.SEND_PROD_IMAGE_EMAIL@DB_LINK('''||v_con||''','||v_review_id||');');


ERROR at line 1:
ORA-06550: line 1, column 93:
PLS-00201: identifier 'PKG_EMAILS.SEND_PROD_IMAGE_EMAIL' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at "XXX.TRG_UPDATE_CON", line 29
ORA-04088: error during execution of trigger 'XXX.TRG_UPDATE_CON'

ops$tkyte%ORA11GR2> create table job_parms( job_id number primary key, con varchar2(30), review_id varchar2(30) ) organization index;

Table created.

<b>we want to be BIND VARIABLE friendly - so we'll use this table in the local database to hold our inputs to the remote procedure</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure local_procedure( p_job in number )
  2  as
  3          l_rec job_parms%rowtype;
  4  begin
  5          select * into l_rec from job_parms where job_id = local_procedure.p_JOB;
  6          my_pkg.p@ora11gr2@loopback( l_rec.con, l_rec.review_id );
  7  end;
  8  /

Procedure created.
<b>this local procedure will be called by dbms_job and sent the unique job id.  It'll use that to find the inputs to the remote procedure and then call it</b>


ops$tkyte%ORA11GR2> declare
  2          l_job         number;
  3          v_con         varchar2(30) := 'Hello';
  4          v_review_id   varchar2(30) := 'World';
  5  begin
  6          dbms_job.submit( l_job, 'local_procedure( JOB );' );
  7          insert into job_parms (job_id, con, review_id ) values ( l_job, v_con, v_review_id );
  8  end;
  9  /

PL/SQL procedure successfully completed.

<b>that is what your block of code  to submit the job will look like, it'll schedule the job (the local routine) and send it the JOB parameter available to the dbms_job block of code.  It then puts the parameters into the job_parms table with the unique job id that was just assigned.

When we commit:</b>
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> commit;

Commit complete.

<b>the job is free to run...</b>

Tom:

very nice solution.

I did schedule the job locally but without bind variables.
It runs pretty fast without binds but is better programming practice to do things with binds all the time.

I had to do run grant execute in remote for this to work too, as i kept getting undefined variable declaration error.

REMOTE DB> GRANT EXECUTE on My_PKG.MY_PROC to PUBLIC

Tom:

I tried it before and it did not recognize the remote user. It seems you cant grant execute for a remote user account.

I did

GRANT execute on PKG.PROV to adminuser@db_link

it did not work

so i did the PUBLIC and it worked.

Tom:

Oh, thanks, i missed that part.

THe package is in remote DB under user A, and the db link is using user B on remote site.

I just need to grant execute on package to B since the link uses that account.

Tom:

One problem i ran to using your method of using JOB_ID as primary key in email log tables and others is it would fail on select when i refresh TEST with prod data.

The job id in TEST would not be in SYNC with the data in the table.

Id it possible to change this automatic job number and should i do it whenever i refresh selected tables so they stay in SYNC. I think a complete refresh using export/import will sync it but not copying table to table over a link.

Tom:

I thought we better test things in a prod-like environment.

What you say make sense too.

But is there really a way to control this job number that oracle get automatically from somewhere. is it s sequence number generator that can be modified?

Hi Tom,

when i run the job, getting the below error
SQL> exec dbms_job.run(13229); 
BEGIN dbms_job.run(13229); END;
*
ERROR at line 1:
ORA-23421: job number 13229 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 582
ORA-06512: at "SYS.DBMS_JOB", line 265
ORA-06512: at line 1
but the job is there in dba_job queue

select JOB,SCHEMA_USER,INSTANCE,BROKEN from dba_jobs where JOB=13229;

       JOB SCHEMA_USER                      INSTANCE B
---------- ------------------------------ ---------- -
     13229 CMS                                     0 N
can you please advise me on this issue.

Thanks,
Ramachari.

scott%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> 
scott%ORA11GR2> variable n number
scott%ORA11GR2> 
scott%ORA11GR2> exec dbms_job.submit( :n, 'null;', sysdate+1 );

PL/SQL procedure successfully completed.

scott%ORA11GR2> print :n

         N
----------
       106

scott%ORA11GR2> 
scott%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select JOB,SCHEMA_USER,INSTANCE,BROKEN from dba_jobs where job = :n;

       JOB SCHEMA_USER                      INSTANCE B
---------- ------------------------------ ---------- -
       106 SCOTT                                   0 N

ops$tkyte%ORA11GR2> exec dbms_job.run(:n);
BEGIN dbms_job.run(:n); END;

*
ERROR at line 1:
ORA-23421: job number 106 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at line 1


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> exec dbms_job.run(:n);

PL/SQL procedure successfully completed.

Hi tom, i need some help, i want to run a procedure every 12th of every month, how do i do that :)?

Hi ,

I want to schedule a procedure at 2.00 pm daily from today.
For this,we have written the below code.
But the procedure did not run.Pls. help

declare
v_job_no number;
dbms_job.submit(job=>v_job_no,
what=>'proc;',
next_date=>trunc(sysdate+1)+14/24,
interval=>'sysdate+1 ');
commit;
dbms_output.put_line(v_job_no);
end;
/

Hi Tom,

I used to have a procedure which would submit a series of jobs (one time only run) to process a pl/sql proc.

All was fine.

Now for some reason the same does not work.

After review of all parameters - cannot see any change - I checked all sorts of forums and tried various tips and checks but to no avail.

When I check user_jobs I see field next date set but NOT this date (which it should when I fire the job right away)

It just hangs there...

However - when I exec dbms_job.run - it works although it blocks the SQL session (FOREGROUND) until it completes - which basically defeats the purpose of having the jobs in the first place.

I saw some reference - made on your site - to execute
DBMS_IJOB.SET_ENABLED(TRUE) - but this does not change anything.

How can I find out what is wrong with the current environment that my jobs do not get started.

Thanks
A

Thank you Tom,

you have confirmed my thoughts.

As this process used to work OK like clockwork for months on end and now it does NOT = = AND the job processes are defined as 1000 (as they used to be) - I am wondering what if anything a junior DBA might have done to cause this...

I would like to check this first before getting in touch with Oracle Support - so I am reaching out to you for advice

Thanks
A

OK Tom,

The problem I am having is that the DBA group does not wish to cooperate + I am not entitled to contact support (no CSI etc.) and they imply that our Apps have a bug.

There is no room to pursue this...

That's why I asked if you could possibly guess what could have been done to dbms_job ...

Thanks
A

Hi Tom,

I'm trying to clean bunch of broken jobs that was created long back. I wanted to drop the jobs created by other user. We are using oracle 11g and I tried 

DBMS_JOB.REMOVE(X.JOB);
&
SYS.DBMS_JOB.REMOVE(JOB);
Below is the error message that I"m getting


SQL>  exec SYS.DBMS_IJOB.REMOVE(990043);
BEGIN SYS.DBMS_IJOB.REMOVE(990043); END;

*
ERROR at line 1:
ORA-23421: job number 990043 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_IJOB", line 360
ORA-06512: at line 1


SQL> exec DBMS_IJOB.REMOVE(990043);
BEGIN DBMS_IJOB.REMOVE(990043); END;

*
ERROR at line 1:
ORA-23421: job number 990043 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_IJOB", line 360
ORA-06512: at line 1