Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Suresh.

Asked: May 11, 2001 - 9:40 pm UTC

Last updated: July 01, 2013 - 9:00 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom,
I am required to run a procedure "analyze_user" at 9 AM every morning. For this I am submitting a job as shown below.

BEGIN
DECLARE
v_JobNum NUMBER;
BEGIN
DBMS_JOB.SUBMIT(v_JobNum,'analyze_user;',sysdate,'sysdate +1');
commit;
end;
END;

If I run the above block and submit a job by 10 AM , then is this job going to be executed every morning 10 AM? If so, how do I make it run at 9 AM every morning, irrespective of the time that I submitted the job. Thanks a lot.

and Tom said...

The old "sliding job". You want to make sure your times are anchored -- fixed -- so that no matter WHEN they are evaluated, they give the correct date/time.

In your case, the interval should be:

'trunc(sysdate)+1+9/24'

take todays date, put it back to midnight, add one day (tomorrow at midnight) and then add 9 hours.

Rating

  (164 ratings)

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

Comments

DBMS_JOB.SUBMIT doesn't start a job whatever we do

john, March 14, 2002 - 8:38 am UTC

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

...?


Tom Kyte
March 14, 2002 - 9:35 am UTC


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've been that easy

A reader, March 15, 2002 - 8:43 am UTC

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...




Tom Kyte
March 15, 2002 - 2:11 pm UTC

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'




Here we go

john, March 18, 2002 - 3:27 am UTC

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. 

Tom Kyte
March 18, 2002 - 7:55 am UTC

If you do a "ps" in your OS (hopefully you are unix) do you see the SNP processes?

eg:

$ ps -auxww | grep snp._$ORACLE_SID
ora817 25484 0.1 5.610719285056 ? S 07:54:16 0:00 ora_snp0_ora817dev
ora817 25488 0.1 5.510716082904 ? S 07:54:16 0:00 ora_snp2_ora817dev
ora817 25490 0.1 5.510716082904 ? S 07:54:16 0:00 ora_snp3_ora817dev
ora817 25486 0.1 5.510716082992 ? S 07:54:16 0:00 ora_snp1_ora817dev
ora817 25492 0.1 5.510716082904 ? S 07:54:16 0:00 ora_snp4_ora817dev
tkyte 25500 0.0 0.1 984 712 pts/2 S 07:54:46 0:00 grep snp._ora817dev

shows I have 5 running snp processes. Do you see the snp's on your system. If not unix, fill in the blanks (OS, version, dbversion, etc etc)

We are NT.

john, March 18, 2002 - 8:33 am UTC

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)

 

Tom Kyte
March 18, 2002 - 9:24 am UTC

Ok -- do the jobs run now?

some more output

A reader, March 19, 2002 - 3:44 am UTC

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.


 

Tom Kyte
March 19, 2002 - 8:44 am UTC

One more thing -- what happens if you

select * from dba_jobs_running;

is it possible to shutdown/restart to see if they are "stuck" on something?

Also -- its getting close to the time to open a tar with support. We might not be able to resolve this here. Consider doing that as well.

Thank you

john, March 19, 2002 - 9:12 am UTC

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.

DBA_JOBS_RUNNING

Gururaj Kulkarni, October 04, 2002 - 3:32 am UTC

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 Kyte
October 04, 2002 - 8:20 am UTC

the synonym points to the view, the view queries some tables.

If you are logged in NOT AS SYS, you will be using the synonym to query the view to query the tables.

If you are logged in AS SYS, you will be using the view to query the tables directly.

DBMS_JOB.CHANGE

sm, October 24, 2002 - 2:32 pm UTC

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

Tom Kyte
October 24, 2002 - 3:23 pm UTC

what's the problem exactly?

are they running (refreshing) as expected?

dba_snapshots.start_with

sm, October 24, 2002 - 4:58 pm UTC

snapshot ran..bt dba_snapshot.start_with column was null

Tom Kyte
October 24, 2002 - 6:52 pm UTC

dba_snapshots is superceded by dba_mviews, try that one

dbms_job

Ram, October 25, 2002 - 6:08 am UTC

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 !

Tom Kyte
October 26, 2002 - 11:31 am UTC

Instead of (or in addition to) doing an update, I would submit a JOB via DBMS_JOB to process the tables.

That would do it.

See the supplied packages guide that documents all of the dbms_* and utl_* packages for details.

dba_mviews

sm, October 25, 2002 - 10:42 am UTC

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


Tom Kyte
October 26, 2002 - 11:44 am UTC

contact support, open a tar.

DBMS_JOB.SUBMIT

George Thomas, October 29, 2002 - 9:52 am UTC

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.

Tom Kyte
October 29, 2002 - 1:53 pm UTC

you didn't restart the database.

do this:

show parameter job


if job queue processes is not set - or job queue interval is too big -- no go. you need to restart after changing some parameters (you can alter system set job_queue_processes=n without restarting!)

if all is "correct", please contact support and open a tar -- they'll need various things like "version of software" and such.



DBMS_JOB.SUBMIT

George Thomas, November 07, 2002 - 9:24 am UTC

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.



Prioritize jobs

A reader, December 17, 2002 - 2:27 am UTC

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

Tom Kyte
December 17, 2002 - 7:58 am UTC

read about the resource manager in the server admin guide.

only on week days ?

A reader, December 17, 2002 - 10:20 am UTC

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,

Tom Kyte
December 18, 2002 - 10:31 am UTC

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>

 

won't it submit the job for 3 - times ??

A reader, December 17, 2002 - 11:53 am UTC


Not in week days

Connor McDonald, December 17, 2002 - 5:32 pm UTC

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

or thereabouts

hth
connor

Just a query

Bhavesh, December 17, 2002 - 8:07 pm UTC

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


Tom Kyte
December 18, 2002 - 11:01 am UTC

interval should be

'trunc(sysdate,''hh'')+1/24'

dbms_job

umesh, December 18, 2002 - 7:02 am UTC

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?

Tom Kyte
December 18, 2002 - 11:08 am UTC

read the admin guide, the resource manager.

why 3- times ?

A reader, December 30, 2002 - 11:01 am UTC

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,


Tom Kyte
December 30, 2002 - 12:07 pm UTC

It wont be -- that is just showing that if:

CURRENT DATE
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 <---

the current date is fri, next will be mon. Suppose however the DB was down on friday for some reason and the job runs on SAT -- the next date will be MON. Or it was down on Fri and Sat -- then runs on SUN -- the next date will be SUN.

This is just showing that no matter when the job runs -- the next SCHEDULED run will be when you wanted it to me.

cool

A reader, December 30, 2002 - 12:09 pm UTC


Why

A reader, February 06, 2003 - 10:24 am UTC

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


Tom Kyte
February 06, 2003 - 10:48 am UTC

No idea what ls_success is.

or syadate for that matter ;)


You would submit a block that looks like this:


'declare
l_out_arg varchar2(20);
begin
package_1.proc_1( sysdate, 1, l_out_arg );
end;'

in any case -- when you want to run a job that gives OUT parameters, you have to submit a block that defines storage for them.

More

A reader, February 06, 2003 - 11:00 am UTC

Thanks for you usful response.

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

THanks


Tom Kyte
February 06, 2003 - 11:18 am UTC

looks ok to me.

concurret jobs

umesh, February 28, 2003 - 4:48 am UTC

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


Tom Kyte
February 28, 2003 - 10:02 am UTC


Better then that -- I can show you the documentation:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/part5.htm#435958 <code>

A reader, May 07, 2003 - 4:32 am UTC

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')?

Tom Kyte
May 07, 2003 - 7:59 am UTC

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> 

A reader, May 08, 2003 - 4:28 pm UTC

GREAT JOB: thank you so much!

A reader, May 09, 2003 - 1:42 pm UTC

""
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 Kyte
May 09, 2003 - 3:09 pm UTC

i wanted them to wait 30 seconds to see if the status of the job changed.

Setting up a job from a different schema

A reader, July 26, 2003 - 2:48 pm UTC

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 Kyte
July 26, 2003 - 4:22 pm UTC

I believe schema_b has the rights to run schema_a's procedure via a ROLE and roles are not enabled during job execution.

Grant execute on p_run_proc to schema_b and retry.

Gr8

Bipin Ganar, July 30, 2003 - 5:31 am UTC

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

Tom Kyte
July 30, 2003 - 7:34 am UTC



and if you use


trunc(sysdate)+1+6/24


it'll do so? not sure what you are saying -- or are you saying "if the job fails, I would like it to run an hour later"? if so, submit THIS as the job:


begin
your_procedure;
next_date := trunc(sysdate)+1+6/24;
exception
when others
then
next_date := trunc(sysdate,'hh')+1/24;
(might email yourself or write a message into a log somewhere)
end;



Use the ability for the job to schedule its next run by setting next_date itself.


Very Nice

R.Chacravarthi, August 30, 2003 - 3:19 am UTC

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.

 

Tom Kyte
August 30, 2003 - 10:49 am UTC

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

 

Need urgent help on the dead job queue system.

Richard Tan, December 09, 2003 - 10:50 am UTC

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.

 

Tom Kyte
December 09, 2003 - 2:23 pm UTC

AQ and dbms_job are left and right, nothing to do with eachother at all.


try setting job queue processes to 0 and then back to >0, see if that does not "touch it" and get it going.

Follow up the last question

RICHARD TAN, December 09, 2003 - 4:21 pm UTC

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...

Tom Kyte
December 10, 2003 - 2:42 am UTC

you'll either have to

a) bounce the instance
b) contact support and open a tar

(by the way, what did you do to your system to get "_" parameters to come out like that. you didn't play with the v$ views or anything did you?)

Follow up

RICHARD TAN, December 10, 2003 - 11:27 am UTC

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.



Tom Kyte
December 10, 2003 - 3:50 pm UTC

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????

 

Follow up

RICHARD TAN, December 10, 2003 - 4:16 pm UTC

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.

 

Tom Kyte
December 10, 2003 - 4:25 pm UTC

it is a HIDDEN PARAMETER in 9i - you are NOT to set it at all. get rid of it.

I know it is "deprecated", you are not to set it at all.

what other "_" parameters and events are you setting (and why)

what is the output of

alter session set nls_date_format='dd-mon-yyyy hh24:mi';
select what, broken, next_date, last_date, sysdate from user_jobs;





Follow up

RICHARD TAN, December 11, 2003 - 10:58 am UTC

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. 

Tom Kyte
December 11, 2003 - 12:29 pm UTC

as for the "number to have" -- that is 100% entirely up to you. you and only you know what your system is

a) responsble for doing
b) capable of doing



one more thing to ask

select count(*) from dba_jobs_running;

what does that show?

follow up

RICHARD TAN, December 12, 2003 - 11:13 am UTC

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. 

Tom Kyte
December 13, 2003 - 11:00 am UTC

going to refer you to support at this point in time. sorry, nothing more i can do here. everything looks OK from this end so far.

Follow up

Richard Tan, December 17, 2003 - 10:09 am UTC

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.

pass two parameters to a procedure that is invoked from a job

Ram, February 20, 2004 - 11:54 am UTC

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

Tom Kyte
February 20, 2004 - 1:22 pm UTC

well, that string would translate into:


tsa_archiveDoc( "Auto Archive", 55 );

"Auto Archive" would have to be "an identifier" since you have it in quotes. But-- in order to not SLAM your shared pool with unique jobs, I would strongly (really strongly) encourage you to do the following:


create table archiveDoc_parms( job_id number primary key,
name varchar2(64),
docid );


and then, schedule the job like this:

...
begin
dbms_job.submit( l_job, 'tsa_archiveDoc( JOB );', sysdate+b/24 );
insert into archiveDoc_parms( doccoment, docid );
end;


and code the procedure as:

create procedure tsa_archiveDoc( p_job in number )
is
l_rec archiveDoc_parms%rowtype;
begin
select * into l_red from archiveDoc_parms where job_id = p_job;
...... process .....
delete from archiveDoc_parms where job_id = p_job;
commit;
end;
/


In that way, you'll be bind variable "friendly" and not have to worry about how to properly construct a character string literal with quotes and all....

whats wrong with my code ??

sonali, April 20, 2004 - 2:21 pm UTC

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


Tom Kyte
April 21, 2004 - 11:54 am UTC

hard to say -- since I don't think the code you are showing is the code being run (the error stack would be hidden by the WHEN OTHERS -- I hate when others, wish we did not have it, please get rid of it, you need it NOT at all -- not even to rollback (that'll happen for you).....


so, can you tell us which dbms_job call is actually failing, what is line 44 of your code.

It does look like this one:

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


the 4th parameter is an interval -- you are sending a "fixed date".

I think you mean:

'add_months(sysdate,3)'


to have it be every 3 months. interval is a string that is a function that when evaluated will result in some time in the future. you sent a "date", not a function that returns a date.

here is the revised code

sonali, April 21, 2004 - 1:03 pm UTC

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

Tom Kyte
April 21, 2004 - 8:59 pm UTC

do you have expert one on one Oracle?

Your book did help me a lot

sonali, April 22, 2004 - 3:26 pm UTC

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

Tom Kyte
April 23, 2004 - 9:13 am UTC

read my section on dbms_job in the appendix.

I do not pass any parameters other than JOB and maybe NEXT_DATE to a job. the other parameters all go into a parameter table keyed by JOB (to make it bind variable friendly).

review

Sven, June 09, 2004 - 5:00 am UTC

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

Tom Kyte
June 09, 2004 - 8:51 am UTC

if they are not running, they are consuming "nothing", they are just rows in a table.

review

sven, June 09, 2004 - 11:33 am UTC

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

Tom Kyte
June 09, 2004 - 11:50 am UTC

means that idle events -- waits backgrounds do while waiting to be told what to do -- are your biggest waits.

meaning, that wait isn't relevant, it is what job queue processes do while waiting to be told to do something.

It should be in

perfstat@ORA9IR2> select * from STATS$IDLE_EVENT;

EVENT
----------------------------------------------------------------
smon timer
pmon timer
rdbms ipc message
Null event
parallel query dequeue
pipe get
client message
SQL*Net message to client
SQL*Net message from client
SQL*Net more data from client
dispatcher timer
virtual circuit status
lock manager wait for remote message
PX Idle Wait
PX Deq: Execution Msg
PX Deq: Table Q Normal
wakeup time manager
slave wait
i/o slave wait
jobq slave wait
null event
gcs remote message
gcs for action
ges remote message
queue messages

25 rows selected.


in the perfstat schema (eg: not on your report). If not, just add it.

is this possible?

Dave, June 14, 2004 - 5:52 pm UTC

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,




Tom Kyte
June 15, 2004 - 8:29 am UTC

I always use parameter table(s) for my jobs

create table job_parameters( job_id number primary key, ...... );
(in your case, a details table, or maybe -- just maybe, this would be OK for a varray column, might be the first application of varray's I would say are "ok" for tables)


and then:

...
begin
dbms_job.submit( l_job, 'proc( JOB );', ... );
insert into job_parameters ( job_id, ... ) values ( l_job, .... );
.....


and the procedure is like:

procedure proc( p_job in number )
is
l_rec job_parameters%rowtype;
begin
select * into l_rec from job_parameters where job_id = p_job;
...
delete from job_parameters where job_id = p_job;
commit;
end;
/



I got job schedule working but just discoverd another problem

sonali, August 31, 2004 - 2:33 pm UTC

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

Tom Kyte
August 31, 2004 - 3:00 pm UTC

insufficient context here -- not sure what this is?

let me try

sonali, August 31, 2004 - 3:11 pm UTC

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


Tom Kyte
August 31, 2004 - 3:41 pm UTC

why shouldn't they or why wouldn't they???

seems to me they should be on the same schedule?? you've just copied them?


I guess you have a very custom, very specific need and a generic "please copy my stuff" isn't what you want. You would need to run a script after import to "remove" the schedules.

or i'm just totally missing something -- you seem to be saying "the next date info is coming over"

<quote>
atleast I don't know how to keep the job schedules coming over
during import
</quote>

but also that they are not coming over. i'm missing something here big time.

more information

sonali, August 31, 2004 - 3:42 pm UTC

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 ?

Tom Kyte
August 31, 2004 - 3:49 pm UTC

that is a horse of an entirely different color no?? (yes it is, rhetorical question -- seems to have almost nothing to do with the above -- other than they both have dbms_job in them)

anyway -- import/export don't work like this, this will not work with import/export. You'll have to come up with a "copy job from user1 to user2" procedure if that is what you want. export/import by design try to recreate the job using the same specification -- the same exact inputs. it is what they do.

You'll need to come up with a custom solution for this, if you really need to "copy jobs"

can use: dbms_job.user_export(job in binary_integer , mycall in out vachar2) ;

pasko, September 01, 2004 - 4:03 am UTC

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.



Tom Kyte
September 01, 2004 - 8:22 am UTC

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?) 

DBMS_JOB and USER_JOBS

Serge Shmygelsky, October 12, 2004 - 5:23 am UTC

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. 

Tom Kyte
October 12, 2004 - 8:24 am UTC

as soon as the job completes, by definiton the row in user_jobs "goes away", it removes itself.

if you need to audit job execution -- you need to put an insert or two into your job (eg: make some note of what you did in your own log/audit tables).

That the job "is gone" is evidence that "it ran"

Jobs audit - ctd

Serge Shmygelsky, October 12, 2004 - 10:30 am UTC

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. 

Tom Kyte
October 12, 2004 - 10:37 am UTC

your JOB itself would have an insert into some table that you wanted to keep track of such things it.

Otherwise, dbms_job.submit without an interval is much like "&" in unix -- not like cron.

The analogy would be with "nohup blah &", not with cron.


blah would have to "echo" something to a file to let you know what happened.

Your submitted job would have to insert something somewhere to let you know what happened.


(stop touching SYS objects -- don't go there, bad bad bad)

Jobs audit - ctd

Serge Shmygelsky, October 12, 2004 - 10:52 am UTC

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? 

Tom Kyte
October 12, 2004 - 11:22 am UTC

you have to "log your OWN messages"

any procedure that uses dbms_job
and that wants to leave a bread crumb behind
would have to do this


or you would write a "wrapper" that would:

create procedure background_it( p_what in varchar2 )
as
l_job number;
begin
dbms_job.submit( l_job,
'call_audit_start; ' || p_what || ' call_audit_stop;' );
end;


or whatever.

dbms_job.submit

Thomas, December 13, 2004 - 10:02 am UTC

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)

Tom Kyte
December 13, 2004 - 10:25 am UTC

don't use SYS or SYSTEM, they are our accounts, stay far away from them.


but in any case, perhaps you

a) logged in as system
b) queued the job up
c) tried to see it in all_jobs from another session

and you didn't commit it? it works fine for system:

system@ORA9IR2> variable n number
system@ORA9IR2> exec dbms_job.submit( :n, 'null;' );

PL/SQL procedure successfully completed.

system@ORA9IR2> COMMIT;

Commit complete.

system@ORA9IR2> select job, :n from all_jobs;

JOB :N
---------- ----------
41 41



Veryyyyyyyy Good artical

Kuresh Kapadia, February 10, 2005 - 2:06 pm UTC

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

Could I submit jobs like this?

Steven, March 07, 2005 - 1:59 am UTC

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





Tom Kyte
March 07, 2005 - 8:01 am UTC

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
 

 
 

dbms_ijob would be easier

Andrew, March 07, 2005 - 11:07 am UTC

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 Kyte
March 07, 2005 - 7:47 pm UTC

the scheduler in 10g changes everything.

strange alert log

Serge, March 17, 2005 - 2:36 pm UTC

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



Tom Kyte
March 17, 2005 - 3:52 pm UTC

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. 

strange alert log ---> phantom jobs

Serge, March 17, 2005 - 3:14 pm UTC

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

Tom Kyte
March 17, 2005 - 3:56 pm UTC

see who owns them, they are not phantoms -- someone scheduled them.

How can I emulate the Java Thread.sleep() method?

richard, April 04, 2005 - 6:49 am UTC

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.

Tom Kyte
April 04, 2005 - 7:37 am UTC

dbms_lock.sleep(...)

no java necessary.

beware dbms_lock.sleep!

Chris Poole, April 04, 2005 - 8:25 am UTC

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!

question regarding "dbms_job.submit

Denise, April 13, 2005 - 2:48 pm UTC

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

what is the nls_env in dbms_job

joe, May 06, 2005 - 8:55 am UTC

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

Tom Kyte
May 06, 2005 - 9:39 am UTC

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. 

Where does "JOB" come from ?

Alex, June 09, 2005 - 7:41 pm UTC

Tom

In your example,

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

.
.
.

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

Thanks



Tom Kyte
June 09, 2005 - 10:16 pm UTC

From expert one on one Oracle:

Custom Scheduling

There are times, such as the above, where the NEXT_DATE is hard to compute in a simple SQL statement or where the next time the job runs is dependent on some complex procedural set of rules. In this case, we can have the job itself set the next date to run.

If you recall from above, the PLSQL block that runs a job is:

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

We have already seen how we can make use of the fact that JOB is available there in the “running a job once” section. We can use it as a primary key into a parameter table to make maximum use of Shared SQL. Well, we can also make use of the NEXT_DATE variable as well. As you can see in the above block of code, Oracle uses the bind variable :mydate as an INPUT into the routine – to set the NEXT_DATE variable – but it also retrieves this value after WHAT (your procedure) executes. If your procedure happens to modify this value – the value of NEXT_DATE – Oracle will use that as the next date to run the job. As an example, we’ll set up a small procedure P that will write some informative message to a table T and set its NEXT_DATE


I Dont have any idea why?

Hawk, July 13, 2005 - 5:58 pm UTC

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.


Tom Kyte
July 14, 2005 - 10:06 am UTC

Perhaps you've done this nasty thing to yourself (created something called schema_aa in schema_aa)

a@ORA9IR2> create or replace procedure proc_a
2 as
3 begin
4 null;
5 end;
6 /

Procedure created.

a@ORA9IR2> grant execute on proc_a to b;

Grant succeeded.

a@ORA9IR2>
a@ORA9IR2> begin
2 a.proc_a;
3 end;
4 /

PL/SQL procedure successfully completed.

a@ORA9IR2>
a@ORA9IR2> create or replace package a
2 as
3 procedure isnt_this_nasty_of_me;
4 end;
5 /

Package created.

a@ORA9IR2>
a@ORA9IR2> begin
2 a.proc_a;
3 end;
4 /
a.proc_a;
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00302: component 'PROC_A' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2>
b@ORA9IR2> begin
2 a.proc_a;
3 end;
4 /

PL/SQL procedure successfully completed.


To be precise in my above question

Hawk, July 14, 2005 - 9:13 am UTC

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

Thankyou very much.

Tom Kyte
July 14, 2005 - 11:05 am UTC

ah, then "think about it"

in AA

what does

AA.X

mean when you have a schema AA and a table AA. that would be (table AA).x

but AA probably doesn't have X and if it did, table.column is not "executable" anyway.


Pretty bad concept having the table and schema named the same no?

Yeap TOM .......I have done that big time

Hawk, July 14, 2005 - 10:52 am UTC

:-)
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

Job Created but not fired

MSU, July 22, 2005 - 7:52 am UTC

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.

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

Tom Kyte
July 22, 2005 - 9:02 am UTC

my car won't start.

I won't tell you what noise it makes when I try.
I won't tell you what the dashboard is telling me.
I won't tell you any of the smells it is making when I try.

but tell me, why won't my car start this morning? I've tried two ways to start it - pushing down a hill and compression starting with the clutch and a key.


care to share the error message and such?

(no binds for the WHAT to jobs, sigh sigh sigh, and sigh......... I'd suggest all jobs should have a SINGLE parameter:

create table job_parms( job_id number primary key, parm1 .., parm2 .., parm3 ...);


and the submit would be:

dbms_job.submit( ?, ?, ? );
get the out parameter which is the first ?
insert into job_parms( ... ) values ( ?, ?, ?, ? )
using that job_id and your parms as binds



and the job would be:

procedure the_job( p_job in number )
is
l_rec job_parms%rowtype;
...
begin
select * into l_rec from job_parms where job_id = p_job;
....



trying to use dbms_lock.sleep in a procedure

A reader, August 02, 2005 - 3:29 pm UTC

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.


Tom Kyte
August 02, 2005 - 4:34 pm UTC

Jobs

Pradikan, August 11, 2005 - 3:30 am UTC

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


Tom Kyte
August 11, 2005 - 9:54 am UTC

I don't know what your "situation" is? You have a developed piece of code that uses many sessions and that session count exceeds what you have configured the database to deal with

choices:

a) fixed your developed code to use less sessions
b) allow for more sessions


the message you are seeing in there is just a WARNING, it says "hey, more work to do than the number of job processes you allowed for can do right now"




pradikan, August 12, 2005 - 3:10 am UTC

Hi Tom,

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

Pradikan


roles are not enabled during job execution

Trevor Ridout, November 24, 2005 - 8:43 am UTC

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

dbms_jobs error

ravi, December 01, 2005 - 11:13 am UTC

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 Kyte
December 01, 2005 - 12:52 pm UTC

dbms_job.submit( :jobno, 'proc;', sysdate, 'sysdate+1' );
^^^

we add:

"begin " || your_string || "end;" to the WHAT and execute it. you missed a ';'

DBMS_JOB.SUBMIT

A reader, January 11, 2006 - 7:58 am UTC

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

Tom Kyte
January 12, 2006 - 10:32 am UTC

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. 

Alter JOB

Jairo Ojeda, January 18, 2006 - 7:24 pm UTC

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

Tom Kyte
January 19, 2006 - 12:31 pm UTC

no, that does not work..

HTML DB Broken Jobs

Shalini, January 24, 2006 - 7:08 am UTC

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

Tom Kyte
January 24, 2006 - 8:18 am UTC

where are you getting that error, what "causes" it, where is it coming from

and - is there in fact a job 1436?

Yes,there is such a job

Shalini, January 24, 2006 - 10:28 pm UTC

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


Tom Kyte
January 25, 2006 - 1:21 pm UTC

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.
 

Its a HTML DB internal job

A reader, January 26, 2006 - 11:25 pm UTC

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. 

Tom Kyte
January 27, 2006 - 8:34 am UTC

(have you considered support for an obvious "support" issue? just asking?)

Or the htmldb forum on otn.oracle.com.

Kill a dba job process

Raghav, February 13, 2006 - 7:25 am UTC

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



Tom Kyte
February 13, 2006 - 8:45 am UTC

dbms_ijob is not for our use.


you would kill the session that is the job, but unless you "break" the job, it'll just run again.

is dbms_job performing an Implicit Commit?

pasko, February 28, 2006 - 12:22 pm UTC

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?


Tom Kyte
March 01, 2006 - 7:43 am UTC

dbms_job.submit does not

HOWEVER, the running of a job does.


when the job is successfully compleleted, it is ended with a COMMIT.

If it fails, it is ended with a ROLLBACK followed by logging of the error.

Thanks Tom

pasko, March 01, 2006 - 9:18 am UTC

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.

Tom Kyte
March 01, 2006 - 9:50 am UTC

I would say "you should commit your transaction when done since transactions should be

a) as big as they need big
b) as small as they can be

Meaning - your transaction is YOUR TRANSACTION, you control it, you should be committing it and not relying on piggy backing onto the jobs transaction that updates the dictionary...

Trace job execution

JLU, March 24, 2006 - 1:37 pm UTC

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

Tom Kyte
March 24, 2006 - 4:15 pm UTC

the jobs are EXECUTED. that I can be very sure of.

your JOBS likely have a

"when others then lets_just_ignore_this_error_and_pretend_we_succeeded_anyway"

exception block.


You can easily do what you ask - just make your job be:

begin
insert into log_table ( 'i am starting...', sysdate );
commit;
the_job_for_real;
insert into log_table( 'i am done...', sysdate );
commit;
end;
/

You'll see they all execute - but you have bad error handling (eg: a bug) in your code that hides the errors.

I'd start by immediately looking for ANY and ALL "when others" that do not also include "raise;"

RE : Trace job execution

JLU, March 27, 2006 - 9:22 am UTC

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

Tom Kyte
March 27, 2006 - 10:11 am UTC

"sorry"

dbms_jobs does not keep a history of one-off jobs, you would have to.

You have a bug in your code - suggest you look for "when others" that hides errors by not being followed by RAISE;

Or, debug it in the fashion I suggested.

RE : Trace job execution

JLU, March 28, 2006 - 5:04 am UTC

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

i need a job

Roderick Henderson, April 28, 2006 - 3:17 pm UTC

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.

job fails if user not sys

A reader, May 30, 2006 - 2:42 am UTC

 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.... 

Tom Kyte
May 30, 2006 - 9:30 am UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

dbms_job runs without roles.

PRIV_USER & LOG_USER in USER_JOBS

Bipin, August 14, 2006 - 5:16 pm UTC

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.

Tom Kyte
August 14, 2006 - 5:36 pm UTC

you sort of have to connect as scott in order to submit a job "as scott"

I don't know why you say "obviously fails", it is not obvious to me (only thing obvious is YOU NEVER USE SYSTEM - don't do that, create your own accounts).

If system has execute on a procedure
then system would be able to submit it.

Sort of Connect?

Bipin, August 14, 2006 - 7:35 pm UTC

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.

Tom Kyte
August 15, 2006 - 7:33 am UTC

setting the current schema just supplies a default schema name for unqualified references.


you have to "connect as scott", period. You have to be scott.


ctl-f for "messy" to see how to be scott without connecting as scott - presumes that scott has a definers rights procedure you can execute that submits the job.

Checking if a stored proc is running through a job.

ht, August 17, 2006 - 6:03 pm UTC

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 Kyte
August 18, 2006 - 7:54 am UTC

I'm not following you at all here.

if proc1 calls proc2, how could proc2 possibly start proc1???


if you schedule A JOB (a single job) then at most one copy of that job will be running.


can you tell us more - it sounds like a bad design decision to have proc2 constantly running - perhaps you meant to use a queuing mechanism or something like that? (rather than monopolizing a CPU forever)

job1 calls proc1 which calls proc2

A reader, August 18, 2006 - 3:09 pm UTC

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?





Tom Kyte
August 18, 2006 - 4:30 pm UTC

why not just schedule proc2 as a job that never ends.

Then the job queue processes will restart it until it "breaks", but you can "unbreak".

Better yet, if proc2 sleeps - why not have it RUN ON A SCHEDULE???

job that never ends

A reader, August 18, 2006 - 5:19 pm UTC

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

Prevention of parallel jobs/procs.

ht, August 21, 2006 - 4:01 pm UTC

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 Kyte
August 27, 2006 - 1:28 pm UTC

1) huh? if you use the job queues and you have it submitted once, at most - it'll run ONCE. Only if someone submits it twice (or more) would you get "parallel jobs"

so the easiest thing to say is "don't do that"

but you could use dbms_lock to allocate a named lock that is released at the end of a session (not during commit). your "job" would try to get that lock and if it could not - it would raise an error saying simply "do not do that" and exit.

2) that'll not stop anyone. I would call this attempt "not smart", "not an intelligent approach". You cannot hide stuff from the DBA like that, they'll figure it out easily enough.

If the DBA's don't think this proc1/proc2 should be running - you better sit down and work this out.



How about 9i?

A reader, September 14, 2006 - 9:15 am UTC

Tom,

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

Cheers,


Tom Kyte
September 14, 2006 - 9:18 am UTC

job_*

just like in 8i. Only there are half as many to set. Like one.

How about 9i?

A reader, September 14, 2006 - 9:48 am UTC

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,

Tom Kyte
September 14, 2006 - 9:57 am UTC

yup

Snapshot not update de column "last_refresh" from dba_snapshots

Samia, October 10, 2006 - 8:30 am UTC

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?



Tom Kyte
October 10, 2006 - 12:03 pm UTC

do not use dba_snapshot, legacy, deprecated. use dbms_mviews.

submit job to execute on the first sun of every month

abraham, February 11, 2007 - 1:06 am UTC

How do i enter a job to be executed on the first Sunday of every month?
Tom Kyte
February 12, 2007 - 10:21 am UTC

use an interval of

next_day(last_day(sysdate),'SUN')



how to pass database link value as parameter to dbms_job.sbumit

Radha, February 12, 2007 - 4:22 pm UTC

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.

===


Tom Kyte
February 13, 2007 - 9:24 am UTC

Well, I prefer to make these things "bind friendly" - so I'll show you that.

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.

so that emulates your code - but just sort of "debugs" what was passed - so we can see it working...

Next, we'll create a table to pass our parameters:

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>

and then submit the job - notice the job we run is an anonymous block, it will access it's unique JOB id and retrieve its parameters, run your procedure, remove its parameters and finish:
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.


now the job will run as normal:

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



and everyone is happy because you are so bind friendly !!!

Tom Fox, February 13, 2007 - 9:34 am UTC

Ok, dumb question. I thought PL/SQL didn't require bind variables. And PL/SQL begins when a BEGIN and END statement exist, correct?
Tom Kyte
February 13, 2007 - 10:09 am UTC

dynamic sql needs bind variables.

dbms_job is all about dynamic sql - you pass an input to dbms_job.submit - a string. The job queue process just executes this string.

Therefore if you:

dbms_job.submit( l_job, 'p(1);' );
dbms_job.submit( l_job, 'p(2);' );
.....

you'll have many "similar but different" strings to execute. So, I say submit the way I did - to create a single string you pass to dbms_job, and let it get it's inputs in a bind friendly way.

Tom Fox, February 13, 2007 - 11:18 am UTC

Gotcha, makes sense now.

Thank you

Radha, February 13, 2007 - 11:30 am UTC

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.




Tom Kyte
February 13, 2007 - 11:50 am UTC

then "concatenate"


dbms_job.submit( l_job, 'p( ''' || p_db_link || ''' );' );


Radha, February 13, 2007 - 1:36 pm UTC

It works great. Thanks Tom.

A reader, February 13, 2007 - 3:09 pm UTC

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



Tom Kyte
February 13, 2007 - 4:21 pm UTC

sqlplus is a very very very simple command line tool, it's logic is:


loop
read a line
execute statement
end loop


it parses each sql statement. A real program would not do that.

Thank you

A reader, February 13, 2007 - 6:37 pm UTC


SQl_id not shown under V$session

Ritesh Malhotra, February 14, 2007 - 1:11 am UTC

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

concept of multithreading in oracle by using dbms_job.

pankaj saxena, March 29, 2007 - 2:02 am UTC

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.

Tom Kyte
March 30, 2007 - 11:56 am UTC

verify that your job_queue_processes is set to at least 4.

How can we run a job everyday from 10.15AM to 12.15PM

Dibyendu, June 07, 2007 - 10:37 am UTC

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.
Tom Kyte
June 07, 2007 - 3:05 pm UTC

... It would be great if you send the code to do this ...

wouldn't it though :)

code a function that when it received a time between between 10:15 and 10:24 - it returns 10:25, between 10:25 and 10:34 - returns 10:35 and so on. When it gets 12:15, it returns tomorrow at 10:15.


create function f( p-date in date ) return date
as
.....


that is your interval for dbms_job.


Thanks Tom

A reader, June 11, 2007 - 2:06 am UTC

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

submit

A reader, May 01, 2008 - 12:29 pm UTC

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 Kyte
May 01, 2008 - 2:46 pm UTC

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.

job

A reader, May 01, 2008 - 12:47 pm UTC

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 Kyte
May 01, 2008 - 2:58 pm UTC

if you have a 1:1 relationship, that screams "one table"

job

A reader, May 01, 2008 - 1:09 pm UTC

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.


Tom Kyte
May 01, 2008 - 3:03 pm UTC

because the sending of the email was totally detached from any *given* process.

you send emails for orders
you send emails for new hires
you send emails for whatever


if the ONLY thing that sent email ever was "orders", you might do it differently. I viewed the sending of email as a process unto itself.

job

A reader, May 01, 2008 - 4:50 pm UTC

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?

job

A reader, May 02, 2008 - 2:54 pm UTC

Tom:
you do not have any recommendation on the above so i can finalize my design in the best way.
Tom Kyte
May 02, 2008 - 3:13 pm UTC

not really... Just use common sense, normalized schemas and as little code as possible.

job

A reader, May 02, 2008 - 3:49 pm UTC

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.

Oracle Queue Scheduler

Jeevan, May 12, 2008 - 10:52 pm UTC

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
Tom Kyte
May 13, 2008 - 10:22 am UTC

show parameter job_queue_process


it is probably zero, no jobs for you until the DBA sets it to non-zero

Job queue

Jeevan, May 13, 2008 - 9:34 pm UTC

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

Jeevan, May 14, 2008 - 10:16 pm UTC

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 Kyte
May 16, 2008 - 12:26 pm UTC

job queue processes


what is it set to.

job_queue_interval is not a hint, it used to be a parameter, it is not adjustable anymore.

job schedule

A reader, June 02, 2008 - 2:04 pm UTC

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 Kyte
June 02, 2008 - 2:42 pm UTC

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

well, that is just wrong - you put that field in the wrong table didn't you?

....
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"?
........

is the goal here to get a sys admin to be able to schedule when the job should run (eg: what happens if they say "this should have run yesterday"....)

why not write the dbms_job package and have an entry point that lets them change the NEXT and have the ORDER table join to the jobs table to pick up the MONTH_PROCESS_DATE for open orders?

job

A reader, June 02, 2008 - 4:58 pm UTC

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.

Tom Kyte
June 02, 2008 - 5:21 pm UTC

fix the design, come on... seriously.


what if they decide to "unextend" the date, put it to a time in the past... it'll never run.

You know how to fix this and it requires fixing the schema.

can you run a little job every day? sure, the overhead would be nominal.

would I want MY NAME on the change history for this - so people into the future think I actually thought to do this in this fashion? Not a chance.

job

A reader, June 02, 2008 - 5:55 pm UTC

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.
Tom Kyte
June 03, 2008 - 10:43 am UTC

...
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.
..


you know, you just ruined the entire day - this is the second thing I read this morning (the first was:

...I do not have any requirements as such. I just have this file and I need to
load this to database. I was wondering if you could help.... )


...
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.
......

because the code is getting beyond embarrassing, starting with the placement of the date field in the wrong table. Ending with the placement of the date field in the table at all.



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



when you create the order row, you schedule the job (a one time job, it'll remove itself from the queue when you are done).

The admin can use dbms_job to reschedule job to some valid point in time in the future.

Until the job runs, then the job disappears and the admin loses the ability to reschedule it of course.


And you are done. And not embarrassed by the implementation anymore.

job

A reader, June 03, 2008 - 1:27 pm UTC

That is Excellent. thanks tom.

Problem with my Job

alex, July 29, 2008 - 5:51 pm UTC

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 Kyte
August 01, 2008 - 10:56 am UTC

what is 30/07/2008, i don't see any formats or anything here.

but regardless, lets see the REAL call you make here. cut and past from sqlplus - it seems you are submitting from sqlplus.

your interval will have this run at midnight tomorrow. check the views - see what the next_date is scheduled to be for your job right now. check failures as well - maybe it did run, but it FAILED, review your alert log.

Strange Job Error

daily reader, November 17, 2008 - 2:25 pm UTC

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 


Tom Kyte
November 18, 2008 - 7:31 pm UTC

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 is a DATE, not a string

you are passing:
next_date => 'SYSDATE+5/1440',


and we are applying the default date mask to that

to_date( 'SYSDATE+5/1440' )

and that fails

so, pass a date

SYSDATE+5/1440

no quotes.

Same error

daily reader, November 18, 2008 - 7:46 pm UTC

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



Tom Kyte
November 21, 2008 - 3:56 pm UTC

what => 'p;'


we wrap a declare/begin and and END block around that....


lots of examples on this page to work from........

to: daily reader from SoCal

Greg, November 19, 2008 - 1:30 pm UTC

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

You da man

daily reader, November 19, 2008 - 1:45 pm UTC


DBMS_JOB to execute twice per hour

Hemal Deshmukh, December 09, 2008 - 6:00 am UTC

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


Tom Kyte
December 09, 2008 - 2:12 pm UTC

it will execute ON or ABOUT or AFTER 14:05 and so on. Jobs never run exactly on time, pretty close many times, but not always.

so, your next could be lines 2-7 here:

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



if it is between 0 and 4 minutes past the hour, next run would be 5 minutes past. If between 5 and 34 minutes past the hour, next run would be 35 past the hour. If between above 34 minutes past the hour, next run is 5 past the next hour....

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.




Does anyone see a problem with the below to reverse engineer jobs ?

Armando Plascencia, January 26, 2009 - 1:56 pm UTC

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)
/

Johny Alex, February 25, 2009 - 6:18 am UTC

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

Tom Kyte
February 25, 2009 - 7:16 am UTC

ummmm......


next_date => sysdate,


??? It is scheduled to run on sysdate first, AND THEN after that, at 1am on every day afterwards...

it just hasn't run yet and the second boundary was crossed during your submit. when it runs, last_date will be set, next will be 1am tomorrow.

Johny Alex, February 25, 2009 - 8:05 am UTC

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
Tom Kyte
February 25, 2009 - 5:54 pm UTC

your your initial time would be trunc(sysdate+1) + 1/24


read the parameter definitions of dbms_job.submit in the documentation

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_job.htm#i1000807


next_date


Next date when the job will be run.


well, if you submit the job at 9am in the morning and use next_date => sysdate, the next date the job will run will be ........

about 9am in the morning on the day you submit it.


therefore, if you want it to run at 1am and it is already past 1am on the current date, next_date => trunc(sysdate+1) + 1/24 would give you 1am TOMORROW, that would be the date you want it to next (first) run.


Your interval is good as is, when the job runs - that interval will be evalauted and that always evaluates to 1am tomorrow (where tomorrow is always variable - it is always one day away)

Bind Variables in dbms_job.submit

VLS, July 07, 2009 - 6:49 am UTC

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
Tom Kyte
July 07, 2009 - 6:25 pm UTC

Regading dbms_job.submit

naga, September 09, 2009 - 3:38 am UTC

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;
Tom Kyte
September 09, 2009 - 6:17 am UTC

the commit let it run, it ran, and then it was gone.

if you don't commit, your session can see it in dba_jobs (but no other session can).

when you commit it - then the job queue process can see it, it saw it, it ran it, it then disappears from dba_jobs. And it just ran too fast for you to see it in dba_jobs_running.

not sure what you expected to see? Unless that procedure takes a LONG TIME to run, you'll never really expect to see it in dba_jobs or dba_jobs_running - it gets into the queue and out of the queue that fast.


why don't you look and see if it didn't run - if it didn't do what it was supposed to have done.

ginkgo, January 12, 2010 - 9:16 pm UTC

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
Tom Kyte
January 18, 2010 - 3:56 pm UTC

you need to commit. to get the jobs to run.


and when it does, you'll see your job is run by a block that looks like this:

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;



where you supply the WHAT. That block of code has access to job, next_date and broken.

It can read the JOB parameter
It can read and write the next_date parameter (to do flexible "next date" processing)
It can write the broken one (to break itself)

DBMS_JOB

Bose, January 31, 2010 - 11:24 am UTC

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 Kyte
February 01, 2010 - 10:25 am UTC

thanks for a bunch of output without any headers?


select interval from dba_jobs to see the interval.


we do not record a first run time, just the last and the next. If you want more logging, you should use dbms_scheduler.

jobs

A reader, February 01, 2010 - 2:48 pm UTC


Vandana, August 26, 2010 - 11:46 pm UTC

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.

Tom Kyte
September 07, 2010 - 7:37 am UTC

dbms_lock can be used, absolutely. It has nothing to do with the job running or not.

Now, is the procedure valid? can it be run?

what does your alert log tell you - anything?

can you run *any* jobs or are all jobs not running?

Vandana, September 13, 2010 - 5:37 am UTC

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 .



submitting job under different user

Adam, December 17, 2010 - 11:08 am UTC

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 Kyte
December 20, 2010 - 7:32 am UTC

dbms_job does not work that way, it won't happen. You have to submit the job as the user you want the job to run as.

job

A reader, March 01, 2011 - 10:03 pm UTC

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.
Tom Kyte
March 02, 2011 - 7:21 am UTC

what would you like to have happen if the job doesn't run on March 31st - but runs on May 30th? (eg: it was 'broken' and not discovered until then).

or is it impossible for it to not be discovered - it will definitely run on March 31st (or does it really run on April 1st - if it started at midnight)

If the latter (assuming it was april 1st really) - then next could just be:

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


your interval would be:

last_day(add_months(trunc(SYSDATE,'mm'),2))

job

sam, March 02, 2011 - 2:36 pm UTC

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 Kyte
March 02, 2011 - 2:49 pm UTC

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.


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



I don't know what your queries above are supposed to be demonstrating?

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.


and what happens when the quick job decides to become the slow job?


There should be one job, that one job would run process1 and then run process2. Now you KNOW they are sequenced properly and you need do nothing "fancy"

job

Sam, March 02, 2011 - 4:03 pm UTC

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 Kyte
March 02, 2011 - 5:42 pm UTC

I do not understand what you mean.

apply critical reading skills here Sam. You said "I will get an email, therefore job will always run on time because *I* will make sure of it"

Now, say you quit - is this process part of the corporate process? Your email goes away. now what? Don't do things this way - it is not a long term solution. Believe it or not - you won't be there forever.


What do you really expect me to do in case oracle has some problem that day.


Plug into your corporate system monitoring, have this be a documented process that goes to a corporate account that is monitored by the DBA of the day, do anything else other then email yourself.


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


put on your thinking cap and think how you might code a job to

a) ask "is it the last day of the quarter
b) if so, then better run part 2
c) else do nothing


How hard would that be?

dump page

Sam, March 08, 2011 - 7:31 pm UTC

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;

Tom Kyte
March 09, 2011 - 7:35 am UTC

There are
very few people on planet who think like you.


that is not true.



Sam - to get a quote into a string, what you do you need to do?


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

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


is one way, this:

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

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


is another.

job

sam, March 09, 2011 - 10:39 am UTC

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 Kyte
March 09, 2011 - 12:42 pm UTC

the error is not coming from your interval.

the error is coming from your 'what'

we just glue 'begin' || what || 'end;' together.

Your 'what' is 'STOCK_REPORTS.RUN_ARCHIVE_REPORT'

it is missing a semi colon.



Our industry is filled with people at and way above me. Think about it...

job

sam, March 09, 2011 - 3:48 pm UTC

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;



Tom Kyte
March 10, 2011 - 9:43 am UTC

if you want extensive tracking of your jobs, you should use dbms_scheduler, it is all builtin.

In your code, what if it is the calls to pkg_emails that is failing?

I'd let the job fail, there are thousands of tools that monitor the alert log - we record the failure there, you would be notified by that.


At best, that should be:
procedure ...
is
   ...
begin
    ....
exception
when others then
    log_error_in_table_with_autonomous_transaction();
    RAISE;
end;


In that fashion, the job will reschedule itself (up to 16 times) and retry the operation. Your error logging table would be a centralized table your developed applications use to write messages to - and you would have a console the DBA team monitors. Sending emails to individuals is OK as a secondary approach - but given the sending of email is a shaky proposition (what if you are out sick, vacation, fired, whatever). Having a logging table is something I like better.

The Heavy Flush

Mark Williams, March 10, 2011 - 4:56 pm UTC

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.

job failure

Sam, March 17, 2011 - 9:24 am UTC

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')
/


Tom Kyte
March 17, 2011 - 9:53 am UTC

there is a bug, but not in our code.

If you evaluate:

trunc(sysdate)+21/24

after 9pm but before midnight - what time do you get? Well, you get 9pm of the current day. But wait - it is ALREADY after 9pm of the current day isn't it? (the job doesn't run until 9pm...) So you are trying to set the date "for times in the past".

Think about it - the error message actually told you precisely what you were doing wrong Sam.

It is like the tides - tide comes in, tide goes out, never a miscommunication - you can't explain that. Well, we can in fact explain that :) (sorry, could not resist that little side joke given the initial sentence in this 'review')

So, yes, we can explain why you job doesn't schedule itself, it is because you are asking to have to scheduled at a prior point in time and that is not allowed.

Try this interval:

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




Don't be so fast to jump the "there must be a bug in the database" - review your code critically - look at the error message - and ask yourself "what happens if I evaluate this at 9:00:01pm, given the error message is telling me it is evaluating to a time in the past"


job

Sam, March 17, 2011 - 10:13 am UTC

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 Kyte
March 17, 2011 - 10:33 am UTC

Sam -

stop.
think.
apply the same logic I just applied.


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?




Your job may run at 9pm.
Your job may run at 12:01am.
Your job may run at 3am.

Your job is ALLOWED TO FIRST START running at 9pm.
There is no guarantee your job will run at 9pm.

So, what would happen if you didn't look at the hour and you just used "trunc(sysdate)+1+21/24"????

What would happen if the job ran between 21:00:00 and 23:59:59? - it would APPEAR to work.

What would happen if the job ran between 00:00:00 and 03:00:00? - it would skip the 9pm job this evening and schedule it for tomorrow.




Same thing is true with your 3am job. If you wanted a job to run at 3am every day - but for whatever reason, the job queues didn't get around processing it until 20 hours later - what would happen? You would not do the job at 3am on that day, you would skip a day and do it later.


With your 9pm job - the margin for skipping a day is even greater, huge in fact. If the job slips by 3 hours or more, you would skip a day. That is why you probably need the case statement.


Think it through. Close your eyes, envision all of the possible situations, extrapolate what they mean.

That is your job, that is what you get paid to do - day in and day out. Believe it or not.

job

Sam, March 17, 2011 - 11:38 am UTC

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 Kyte
March 17, 2011 - 12:30 pm UTC

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.


think about what happens if the database is down, that is the simpliest case.

or if the job queues are really backed up - that is the more complex case.


It does not sound very good if your jobs need to be serialized too (i.e job1
must run before job2)


if that is the case, you would of course be using the SCHEDULER which allows you to build job chains - whereby the second guy only executes after the first guy(s) have successfully executed.


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


No, I just think about what could happen. It wasn't "my logic" here, it was "logic in general"

job

sam, March 17, 2011 - 8:37 pm UTC

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

Tom Kyte
March 18, 2011 - 7:34 am UTC

Sam,

'exec' is not a valid sql command
you do not run 'exec' with a slash
and even if you did, you are missing the closing ')'


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



I have to go our of my way to figure out how you did that one - if you type exec on a line interactively and hit enter, you cannot even get in this situation.

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.



Yes, in fact it works.

Sam - use some critical thinking here. Start reading the error messages a LITTLE closer. The ora-12005 was all you needed above. This error here - pointing to the EXEC word was all you need here.

There are obvious errors in this block of code - missing the closing ')' being a big huge very visible one.

job

sam, March 18, 2011 - 11:16 am UTC

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;

Tom Kyte
March 18, 2011 - 11:47 am UTC

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


ummm, no.


';' works (by default) in SQLPlus for a SQL statement only.

'/' runs (by default) in SQLPlus to run the current buffer which must have a sql or plsql block in it.

they have a subtle difference, they are changeable, and neither are PART of the sql or plsql statement.

SQLPlus commands - such as "set", "show", "execute" - are not terminated by these characters.

with execute, you have:

exec[ute] string

whereby sqlplus does the logical equivalent of:

begin
string
end;
/

It just takes your string and glues a begin on the front, an end; on the end and runs the block of code.


execute is NOT sql, execute works on a single line of input only (never multiple lines) and execute is not ever run with "/"

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




You were NOT using any format I've ever used. You were using EXECute in a manner that would never work - to run a block of code that cannot be compiled. You did NOTHING remotely similar to what I've done.

job

sam, May 24, 2011 - 1:46 pm UTC

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'


Tom Kyte
May 25, 2011 - 11:03 am UTC

it is possible to remotely schedule a job - HOWEVER - I would not recommend it. It would make your local transaction rely on the remote database availability - that is, you would have reduced availability (and the overhead of a two phase commit in the user response time, something you do not need)

I would strongly encourage you to schedule a local stored procedure call - which in turn calls the remote procedure.

I would need details on the database link itself - remember, that block will be run in the background - does it have a user/password associated with it?



this is how I would set it up:


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>

job

A reader, May 26, 2011 - 9:23 am UTC

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 Kyte
May 26, 2011 - 9:28 am UTC

NOT TO PUBLIC - just grant it to the user that owns the stored procedure!!!!!!

JOB

sam, May 26, 2011 - 5:01 pm UTC

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 Kyte
May 26, 2011 - 6:28 pm UTC

no, on the remote site - just grant to adminuser.


On the remote site, when logged in as the DBA on the remote site, grant execute on that package to the user associated with the database link - the database link isn't relevant or used in the grant - just the "user" that uses that database link

if you

create database link foo connect to BOB identified by hello using 'remote_database'

you would log into the remote database and grant execute on pkg to bob;


job

sam, May 26, 2011 - 7:46 pm UTC

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 Kyte
May 27, 2011 - 6:39 am UTC

correct, in the remote database is where you would do this grant.

job number

sam, May 26, 2011 - 7:53 pm UTC

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 Kyte
May 27, 2011 - 6:41 am UTC

why would you sync your jobs table over, it isn't relevant on test. Don't do that - that would be the simple straightforward (and correct) answer.

The data in the job parameter table *cannot be used* on test, it should not be 'refreshed' into test. The only thing that could happen if you did it would be to send the email more than once.

job

sam, May 27, 2011 - 10:09 am UTC

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?
Tom Kyte
May 27, 2011 - 11:04 am UTC

you DO test them in a prod like environment. Think about the definition of the word like in this context sam.


Does it make sense to have the table in test? YES
Does it make sense to have the data from prod in this table in test? NO - totally NO


Think about this sam, thinking caps on. Would it be *safe* to have this data in test? What could come from having this data in test. I can see the job getting scheduled, I can see the job getting run - and that would be *bad* would it not. It is already scheduled and run on the OTHER (production) machine.

You cannot impact the job number, you don't NEED to impact it, you don't WANT to impact it. You just need to think about what makes sense to copy from prod and what doesn't make sense. The contents of your job parameter table DOES NOT MAKE sense to copy.

Ramachari, October 12, 2011 - 12:36 am UTC

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.

Tom Kyte
October 12, 2011 - 6:56 am UTC

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.



You have to own the job to run the job with dbms_job

Run a procedure 12th of every month

Ivan, October 25, 2011 - 10:45 am UTC

Hi tom, i need some help, i want to run a procedure every 12th of every month, how do i do that :)?
Tom Kyte
October 25, 2011 - 12:13 pm UTC

the interval would be:

last_day(trunc(sysdate))+12



just get to the last day of this month, add 12.

dbms_job

jonie, November 15, 2011 - 7:30 am UTC

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;
/

Tom Kyte
November 15, 2011 - 10:46 am UTC

that won't run until tomorrow - and then it will start running later and later and later every day....


you want to start with trunc(sysdate) + 14/24, that is 2pm today (assuming it is before 2pm, you didn't say)

and your interval should be "trunc(sysdate)+1+14/24" - to get it to be 2pm TOMORROW reliably ( read the original answer about the sliding window way above...)

submitted job only runs with dbms_job.run

Andre, March 14, 2013 - 11:04 am UTC

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

Tom Kyte
March 14, 2013 - 1:55 pm UTC

if the job queue is not picking them up and running them - then it is not working correctly (make sure job queue processes is greater than zero)

if it is >0, and the submit was committed and the job never runs, you have a reason to get in touch with support... something is wrong and it is not working as it should

dbms_job not running

Andre, March 15, 2013 - 7:00 am UTC

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

Tom Kyte
March 25, 2013 - 9:41 am UTC

this is so clearly a support issue.

if job queue processes is set properly, the job queue should be running


and Senior DBA's can screw things up even worse than Jr DBA's seem to be able to - they have less fear sometimes (over confidence ;) )

dbms job

Andre, March 26, 2013 - 1:13 pm UTC

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

Tom Kyte
March 27, 2013 - 3:27 pm UTC

scream up through your management chain that the DBA support team, which is there to - you know - support the database, isn't supporting the database.


there is nothing you will be able to do to affect this. You have a clear and convincing test case for them - just send them and your manager and their manager (as high as you need I guess) the test of:


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

sql> now query user_jobs over the period of an hour showing the job never ran


and ask the DBA team to please explain what bug you have in your logic there - because it is the same in the application that isn't working and their reluctance to assist is negatively affecting the day to day operations of your company.

make sure management up both sides of the chain - yours and theirs - sees this after their repeated failure to research the issue.

there is always room to pursue things if you have scientific, irrefutable evidence that "something is happening that should not be happening and I don't have the power to diagnose it - but you do"


This is what i do everyday here.


If presented with such a small case that clearly demonstrates the issue, and if the issue is shown to many people, and if the issue is actually causing a production issue - you'll find your ability to pursue this is huge!


just make sure you have all of the facts, it sounds like you do.

jobs

Prabin, April 16, 2013 - 9:48 pm UTC

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


Tom Kyte
April 22, 2013 - 7:32 pm UTC

jobs can only be managed by the owner of the job.

dbms_jobs

Yiming Li, June 26, 2013 - 7:09 pm UTC

Hi, Tom,

Thanks for your website and give me a lot of help. I just wondering How to use the dbms_job to schedule running a job, every hour, from 6am to 18 pm, and Monday to Friday.

Thank you very much
Tom Kyte
July 01, 2013 - 9:00 pm UTC

easiest to create a function like this:

create or replace function next_time( p_dt in date ) return date
as
    l_friday   varchar2(3) := to_char( to_date('5/7/2013','dd/mm/yyyy'),'dy' );
    l_saturday varchar2(3) := to_char( to_date('6/7/2013','dd/mm/yyyy'),'dy' );
    l_sunday   varchar2(3) := to_char( to_date('7/7/2013','dd/mm/yyyy'),'dy' );
    l_monday   varchar2(3) := to_char( to_date('8/7/2013','dd/mm/yyyy'),'dy' );

    l_day      varchar2(3) := to_char(p_dt,'dy');
    l_hour     number      := to_number( to_char( p_dt, 'hh24' ) );

    l_date     date;
begin

    if (l_day in (l_saturday,l_sunday))
    then
        l_date := next_day( trunc(p_dt), l_monday ) + 6/24;
    elsif (l_hour >= 18 and l_day = l_friday)
    then
        l_date := next_day( trunc(p_dt), l_monday ) + 6/24;
    elsif (l_hour >= 18 )
    then
        l_date := trunc( p_dt )+1+6/24;
    elsif (l_hour < 6 )
    then
        l_date := trunc( p_dt ) + 6/24;
    else
        l_date := trunc( p_dt,'hh')+1/24;
    end if;

    return l_date;
end;
/




and then use the interval "next_time(sysdate)"


Yiming Li, July 18, 2013 - 7:32 pm UTC

Tom,

Thanks for the kindly feedback.
I tried to run the job and got the following ORA-23319 messages.

bcp_owner@ORACP2> ed
Wrote file afiedt.buf

1 declare
2 l_job number;
3 begin
4 dbms_job.submit( job => l_job,
5 what => 'UPDATE_PROT_ACCRUAL;',
6 next_date => sysdate,
7 interval => next_time(sysdate));
8 COMMIT;
9* end;
bcp_owner@ORACP2> /
declare
*
ERROR at line 1:
ORA-23319: parameter value "18-JUL-13" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 60
ORA-06512: at "SYS.DBMS_JOB", line 138
ORA-06512: at line 4

Yiming Li, July 18, 2013 - 9:00 pm UTC

HI, Tom,

I got the problem. Your function works fine.
Many Thanks .


DBMS_JOB.SUBMIT() not working

A.S, January 02, 2014 - 10:35 am UTC

Hi Tom,

Thanks for all the suggestions you have been providing to us. I wrote a trigger to provide read access on a system generated create table event. Prior to this I did not have much idea about how to write trigger. This is providing grant to a particular DB user to that created table. Trigger is created but it seems there is some logical error in the way I used SUBMIT() procedure. After creating the trigger when I checked whether my_user is getting the access or not, I found no. I then removed the Exception to know what is causing the failure of trigger block and while creating the table I got below error:


SQL> create table tab1 as select * from dba_users;
create table tab1 as select * from dba_users
                                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 111:
PLS-00114: identifier 'grant select, insert, update, ' too long
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 140
ORA-06512: at line 16

Trigger body..

Create or replace trigger SYSADM.GRANT_ACCESS_ON_CREATE AFTER CREATE 
ON sysadm.SCHEMA
Declare
oper_type varchar2(150);
created_object varchar2(150);
object_owner varchar2(150);
grant_statement long;
grant_job number;


BEGIN
oper_type := ora_sysevent;
object_owner := ora_dict_obj_owner;
created_object := ora_dict_obj_name;

IF ora_dict_obj_type = 'TABLE' THEN
     grant_statement:='execute immediate "grant select  on '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to my_user";';
     dbms_job.submit(job=>grant_job, what=>grant_statement);
 commit;
  END IF;

EXCEPTION
WHEN OTHERS THEN
  null;

END GRANT_ACCESS_ON_CREATE;
/

Trigger created.

Could you please help me to know what is causing the problem with the SUBMIT();

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library