It worked and Thanks for the same.

Hi, Tom,

I have:
---------
function fork( procname varchar2 ) return binary_integer as
pid binary_integer;
nid binary_integer;
begin
dbms_job.submit( pid, procname, sysdate, null, false );
1---> dbms_job.submit(nid, 'NULL;', sysdate, null, false);
2---> dbms_job.run(nid);
return pid;
end;

where procname is mainly an "insert" statement without "commit".

I use the anonymous pl/SQL block to call the "fork"
function and simulate the multithread process in PL/SQL.

Question:
How the record commited here without any "commit" statement presented? What is the 1 and 2 statements special meaning as shown above? Is it means if we run another job then the formaer job will be commit automatically?

THanks

Tom,
I have a report st. procedure that uses Ref Cursor to pass the cursor (via OO4O) to Excel client.

The procedure does "INSERT INTO ....SELECT" on the GTT...
While its running and it can be few minutes, that makes the Win app *appears* to be hanging. I am thinking of submitting the procedure to DBMS_JOB to run immediately (like your case with sending emails) but testing shows that apparently the GTT data ends up in some other universe.

Is there a way I can make this work ? else I have to decide whether just leave it as is or insert into a regular table.

Thanks

Tom:

I have a sql*plus script that runs queries and spools data to several files. Can I scedule this file to run every week by dbms_job? If no, then do I schedule it using unix cron or convert it to pls/sql which can not sppol data and i have to use more complex UTL_FILE?

Thank you,

Tom:

User enters a request on a web form and it is saved into a request table. One column of the table "assigned" defaults to null until the shipper is ready to ship the material. I need to send an email if the "assigned" colum stayed null (not assigned within two days). How would you implement this?

DO i write a trigger (what kind) that runs via a job every night to check the "assigned" value and send an email based on that?

Tom:

1. I did a before-insert trigger on the request table since that is only type where you can access :new

create or replace trigger send_email
before insert ON SHIPM_REQUEST
FOR EACH ROW


DECLARE
l_job number;
v_data long ;

BEGIN
dbms_job.submit(l_job,'SEND_EMAIL_REQUEST(JOB);',sysdate+2 );

FOR x in (select * from Request where request_id=:new.request_id)
LOOP
v_data := v_data||'Request ID: '||x.request_id||chr(10);
v_data := v_data||'Request Date: '||x.request_date||chr(10);
v_data := v_data||'Contact: '||x.contact||chr(10);
END LOOP;

insert into email_parameters(ID,request_id,request_date,
email_data,request_Section)
values
(l_job,:new.request_id,:new.request_Date,
v_data,:new.request_section);

:new.job_id := l_job;

END;


The problem is that v_data is not picking up any data and the email is not sent. Any ideas what is happening. When I default v_Data to 'test' it gets sent only with that word.

2. On your second suggestion do you mean putting a when others exception like this :

create or replace trigger assign_request
AFTER UPDATE ON REQUEST
FOR EACH ROW
WHEN(new.assigned_to is not null)

BEGIN
dbms_job.remove(:old.job_id);

EXCEPTION
WHEN OTHERS THEN
:new.job_id:=null;
null;
END;

3. If I want to send an email alert if shipment was not done by required date on the request, you would set up a before-insert trigger on request table that runs the job on the required date and then check if there was a hispment record or not?

Thank you,

Tom:

1. AS a followup to #1, I did what you suggested previously where you insert the message usually when you insert into the email_parameters table. I had an error when i did "After-insert" because it did not realize the :new.request_id.

As far as my transaction I insert request id first then all other data.

I will try to move the select statement into the procedure that sends the email but this way I will not be inserting the message to the email_parameter table?

Tom,

Regarding the procedure that Mo has published, I do not see a "commit" in there...Could that be the issue?

Tom:

1. I got the following error when I tried to make it after-insert-trigger
10:43:27 AM Error: ORA-04084: cannot change NEW values for this trigger type

this is resulting from
:new.job_id := l_job;

I tried to change that to an update statement and then I got the mutating error because I am updating the job id for the same table.

Any hints?

Tom:

1. Do you know what may be causing this error in the trace file. Is this something to worry about. Could be the result of scheduling more jobs than my variable allows for (2).

/ora817/admin/ips/snp0_301110_imsd.trc
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
ORACLE_HOME = /ora817
System name: AIX
Node name: xxxx
Release: 3
Version: 4
Machine: xxxxx
Instance name: ips
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 301133, image: oracle@xxxx (SNP0)

*** SESSION ID:(8.25046) 2003-04-28 17:42:56.725
*** 2003-04-28 17:42:56.725
ORA-12012: error on auto execute of job 26
ORA-06533: Subscript beyond count
ORA-06512: at "IPSD.SEND_EMAIL_REQUEST", line 37
ORA-06512: at line 1
*** SESSION ID:(8.25062) 2003-04-28 17:45:00.368
*** 2003-04-28 17:45:00.368
ORA-12012: error on auto execute of job 26
ORA-06533: Subscript beyond count
ORA-06512: at "IMSADMIN.SEND_EMAIL_REQUEST", line 37
ORA-06512: at line 1
*** 2003-04-28 19:03:36.206
*** SESSION ID:(8.25674) 2003-04-28 19:03:36.206
java.lang.NullPointerException: Null buffer
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java)
at java.net.SocketOutputStream.write(SocketOutputStream.java)
at oracle.plsql.net.TCPConnection.write(TCPConnection.java:202)
at oracle.plsql.net.TCPConnection.write(TCPConnection.java:395)
*** 2003-04-28 19:03:36.700
ORA-12012: error on auto execute of job 32
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException: Null buffer
ORA-06512: at "SYS.UTL_TCP", line 678
ORA-06512: at "SYS.UTL_TCP", line 350
ORA-06512: at "SYS.UTL_TCP", line 428
ORA-06512: at "SYS.UTL_SMTP", line 263
ORA-06512: at "IMSADMIN.MAIL_PKG", line 69
ORA-06512: at "IMSADMIN.SEND_EMAIL_REQUEST", line 57

Tom:

WHat is allocated collection bounds in pl/sql and is this something that can be enlarged? I was suspecting a variable I used in the cc_parameter that when it is null (no value) this thing happens. Of course it could be just a coincidence too.

Tom,

I can see an area in a system I work on where some of the techniques above would be very useful but have a few queries, as I think we would end up producing a high number of jobs that may be scheduled for upto 1 year advance.

1) Is there a limit to the number of jobs you would have submitted on one of your systems? 1000, 10000, 100000?

2) Is there a practicle limit on how far in advance you would schedule the jobs, 3 months, 6 months etc?

Thanks

Andy

Tom:

I have two triggers that schedule two jobs after insert of a record. The first one supposed to schedule the job two days after creation date and the other one will schedule it two days after required date. Surprisingly, one record was created May 15 and the first job was scheduled May 19. the second trigger schedule the job May 20 even though required date was May 19. Does oracle skip weekend days when scheduling. How do you explain this?

dbms_job.submit(l_job,'SEND_EMAIL_REQUEST(JOB);',sysdate + 2);

dbms_job.submit(l_job,'SEND_EMAIL_REQUEST_DATE(JOB);',:new.required_Date+2);

REQUEST_ID : 555
REQUEST_DATE : 15-may-2003 14:47:20
REQUIRED_DATE : 15-may-2003 00:00:00
CREATION_DATE : 15-may-2003 14:47:20

JOB : 59
NEXT_DATE : 19-may-2003 10:07:40
NEXT_SEC : 10:07:40
TOTAL_TIME : 35
BROKEN : N
INTERVAL : null
FAILURES : 11
WHAT : SEND_EMAIL_REQUEST_DATE(JOB);
NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
MISC_ENV : 0102000000000000
INSTANCE : 0
-----------------

JOB : 60
LAST_DATE :
LAST_SEC :
THIS_DATE :
THIS_SEC :
NEXT_DATE : 20-may-2003 00:54:33
NEXT_SEC : 00:54:33
TOTAL_TIME : 26
BROKEN : N
INTERVAL : null
FAILURES : 11
WHAT : SEND_EMAIL_REQUEST(JOB);
NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
MISC_ENV : 0102000000000000
INSTANCE : 0
-----------------

Tom:

I tested the code in development instance numerous times and it works fine. I even showed you the real record data.

The only think I suspect is that if they take the system down on Saturday or Sunday Does oracle re-schedule those jobs or do something to it?

2. If i want to skip Saturday and Sunday and work solely based on business days from the scheduling would I check todays date and add days to it based on that or there is an automatic feature in the dbms job?

Thank you,

In your book pp 1065 the code are...

----- start -----
create or replace
PROCEDURE fast_send_mail (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_job number;
begin
dbms_job.submit( l_job, 'background_send_mail( JOB );' );
insert into send_mail_data
( id, sender, recipient, message )
values
( l_job, p_sender, p_recipient, p_message );
end;

-----End-----

I tried hard finding the declaration of JOB (supposedly a constant?) that is found inside

dbms_job.submit( l_job, 'background_send_mail( JOB );' );

will dbms_job.sumbit automatically send it as 'background_send_mail( l_job );' ???

Hi Tom
"I've done single digit thousands -- I've not done 100,000 -- so, I set it up to
see what effect it might have.

I loaded up 100,000 jobs in the job queue. Ran statspack on an otherwise idle
system. After about 8 minutes, the top sqls (all background sql here -- nothing
else going on) were:
"

In the above reply, you suggest that jobs scale pretty well.
One of the arguments I hear is " dbms job takes a long time and consumes much cpu."

I am not sure how to verify this claim
because the argument does not compare it with anything else. The argument is supporting another
argument that is in favor of moving some of the
code that is in the database to middle tier based
on following assumptions:

a. jobs are cpu intensive - if we remove some of the
functionality from jobs to middle tier we can
employ cheaper boxes instead of using more powerful
databases (Which could be costlier)
b. The database mechanism (DBMS_JOBS) is fairly
inefficient compared to Java threading when it comes
to short interval polling.

How can I verify whether these claims are true? How
would you benchmark and verify or refute these claims

Thanx a lot - please keep up the good work!

taken from your book as for jobs:

<quote>
they run in the same environment (user, characterset, and so on) they were submitted in (minus roles).
</quote>

but: jobs always get NULL back from SYS_CONTEXT('USERENV', 'OS_USER') - at least at my local instance.

is this something one can rely on in general to let a certain piece of code identify whether it is executed via by a DBMS job or just the "normal" way?
or if not: what is the best solution to achieve this?

the OS user would be the oracle software owner, it would be constant.  the os user is "gone" by then.

have you looked at fg_job_id with sys_context?

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

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure fg_job_id( p_text in varchar2 )
  2  as
  3  begin
  4          insert into t select p_text || sys_context( 'userenv', 'fg_job_id' ) from dual;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec fg_job_id( 'in plus: ' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> variable n number
ops$tkyte@ORA920> exec dbms_job.submit( :n, 'fg_job_id( ''in job using run: '' );' );

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> print n

         N
----------
    100687

ops$tkyte@ORA920> exec dbms_job.submit( :n, 'fg_job_id( ''in job normal: '' );' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> print n

         N
----------
    100688

ops$tkyte@ORA920> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

MSG
--------------------------------------------------
in plus: 0
in job using run: 100687
in job normal: 100688

 

thank you, tom, for the hint and your sample.

now I've checked the documentation for "FG_JOB_ID":

<quote>
Job ID of the current session if it was established by a client foreground process. Null if the session was not established by a foreground process.
</quote>

I've to admit I didn't really get it from there: I did NOT expect the condition "if it was established by a client FOREground process" to hold true for sessions "doing the work" on behalf of DBMS jobs.
I thought: jobs are "established" by client FOREground processes, but the sessions for those jobs get "established" by a BACKground process (processsing the DBMS jobs).

looking at your real world test case one would come to the conclusion: SYS_CONTEXT('USERENV', 'FG_JOB_ID') returns the ID of the DBMS job (if any) related to the current session or 0 otherwise. is that right?

is the documentation wrong? at least SYS_CONTEXT('USERENV', 'FG_JOB_ID') returns 0 instead of NULL from within the current session.

and why doesn't SYS_CONTEXT('USERENV', OS_USER') return the ORACLE software owner on my LOCAL database?

Dear Mr. Tom ,

 I submit job queue following way :

o/s : Window  NT 4.0
Oracle8 Release 8.0.4.0.0 - Production

variable  l_job number
declare
   l_job number;
begin
    dbms_job.submit( l_job,
                     'BEGIN tuning; END;',
                       trunc(sysdate)+12.25/24, 
                      'trunc(sysdate)+12.25/24',FALSE);
commit;
end;

sql>alter session set nls_date_format='dd-mm-yy:hh:mi:ss'

SQL> select job,
  2  NEXT_DATE ,     
  3  NEXT_SEC ,      
  4  TOTAL_TIME,     
  5  BROKEN  ,       
  6  INTERVAL ,      
  7  FAILURES   
  8  from dba_jobs       
  9  /

      JOB NEXT_DATE         NEXT_SEC TOTAL_TIME B INTERVAL                              
--------- -------------- -------- ---------- - ------------
        1 09-03-99:12:46:01 12:46:01  -3.09E+09 N sysdate+1                             
 -->   87 07-10-03:12:15:00 12:15:00          0 N trunc(sysdate)+12.25/24
       86 07-10-03:04:30:00 16:30:00          3 N trunc(sysdate)+16.5/24                
       88 07-10-03:01:15:00 13:15:00          0 N trunc(sysdate)+13.25/24


in the alert.ora file the following error message appearing  
++++++++++++++++
Tue Oct 07 12:18:18 2003
Errors in file M:\orant\RDBMS80\trace\orclSNP1.TRC:
ORA-12012: error on auto execute of job 87
ORA-12005: may not schedule automatic refresh for times in the past

1. why is there different between next_Date, next_sec time  ?
2. what is reason that above error message is coming or  why job is not  executing  ?

Thanks+Regards 

Tom,

what i'm looking for is how to run a script on the server
so that I can close my client without stopping the executing of the script. Is it possible to do this from a sqlplus client connection somehow? I searched your site for a while but couldn't find what i need.

Does dbms_job scheduled jobs commit automatically or we have to include commit explicitly in the job?

Hi, Tom.

We have a package with several procedures that are run at various times.

Ideally, we'd like to send out an email when a procedure finishes.

The package is on a (production) 8.0.6 box. We have a 9i box, so I've set up a send_mail job on it, and I remotely call it in the 'end_proc' procedure that's part of the package. (end_proc logs the end time and calculates the elapsed time, logs it, and then calls a LOCAL STANDALONE procedure, which in turn calls the REMOTE STANDALONE send_mail procedure.)

This works like a charm when the packaged procedures are called from SQL*Plus.

But when we use dbms_job and SUBMIT procedures in this package, the email never gets sent. All the steps in end_proc prior to the call to send_mail (the local one) get executed.

End_proc has an exception block with log_error in it, but nothing gets logged.

Is there anything special about the way a job run by dbms_jobs would resolve a database link? Here's end_proc:

procedure end_proc (p_procedure_name in varchar2) as

l_procedure varchar2(64);
l_finished date;
l_elapsed number(5,2);
l_run_for date;
l_osuser varchar2(20);
l_message varchar2(2000);
l_cr_lf varchar2(2) := chr(13) || chr(10);



begin

l_procedure
:= p_procedure_name;

-- set the end time and calculate the elapsed time

update cap_proc_log set end_time = sysdate
where ....;

update cap_proc_log set elapsed_time = (end_time - start_time) * 24 * 60;

/* the proc log is small so we just update all the rows rather than searching for the right row */

commit;

-- capture end time for email

select end_time, elapsed_time, run_for_date
into l_finished, l_elapsed, l_run_for
from .....

select osuser
into l_osuser
from v$Session
where audsid = (select userenv ('sessionid')
from dual);

l_message := 'User - ' || l_osuser || l_cr_lf ||......

-- this doesn't happen when submitted through dbms_jobs
send(l_message);


commit;

exception

when others then
log_error;


end end_proc;


Thanks as always.

Figured it out.

If you know how I could include the OS user....


;)

It was this:

select osuser
into l_osuser
from v$Session
where audsid = (select userenv ('sessionid')
from dual);

This was apparently returning more than one row. I wasn't catching it because of the 'bug' : when others then - without a raise.

There are cases elsewhere, in other procedures, where too_many_rows is an expected condition, so I was ignoring them in the error_table, when in fact they were thrown by end_proc and should have been raised.

Now, how would I capture the osuser when the job is submitted?

Tom:

If I have this statement running in instance A but I want the job to be scheduled in instance B how do you do that:

dbms_job.submit(l_job,'SEND_EMAIL(JOB);',sysdate+7);

Thanks

Tom:

Are you sure? I am looking at 8i book and the syntax is:

PROCEDURE SUBMIT(job OUT BINARY_INTGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT NULL,
no_parse IN BOOLEAN DEFAULT FALSE);

Nothing on instance?
I tried to call a procedure from another instance and that does not work either.

Another method if, for some reason, you can't find it in the Oracle docs is just this:

SQL> desc dbms_job

[snip]

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

[snip]

Hey!  Look at that!  There is an 'INSTANCE' parameter...

- Mark 

Tom:

1. The instance parameter is "integer". How can you get instance number when every instance I log into has a number of "1"?

2.
instance= When a job is submitted, specifies which instance can run the job.

The description for this parameter implies that you an specify which instance can run the job. This means that job scedhled is still in "Instance A" but Instance B is allowed to run it.

What I am trying to do is that I have one package in instance A with 12 procedures. One procedure for another app is in instance B that schedules the email and running the email procedure in instance B. I am trying to have the email procedure part of the package in instance A. All I will be doing is submitting job in instance B.

Tom:

I logged in to several instances and the result to:

select value from gv$parameter where name = 'instance_number';

VALUE
-----------------------
0

Correct me if I am wrong:

The instance parameter allows another instance (B) to call the job I scheduled (instance A). This is not what I really want. What I want is to do osomething like this:

I want to run this in instance A (call a procedure in instance B):

dbms_job.submit(l_job,'packageB.SEND_EMAIL(JOB)@b_link;',sysdate+8);

Is it possible? I am getting an error when I try it.

Hi Tom

I have scheduled a job and that is working fine but when I see the data in USER_JOBS table, It does not show any value in TOTAL_TIME column.
Why?

Thanks
Rahul.

do this and show me the output (you'll need DIRECT grant on dbms_lock, not a role.  if no grants use a loop like:

  l_date := sysdate;
  loop
     exit when (sysdate-l_date >= 1/24/60/60*5);
  end loop;



ops$tkyte@ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_lock.sleep(5);
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'p;', sysdate, 'sysdate+1/24/60' ); 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select total_time from user_jobs where job = :n;
 
TOTAL_TIME
----------
         0
 
ops$tkyte@ORA9IR2> exec dbms_job.run(:n)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select total_time from user_jobs where job = :n;
 
TOTAL_TIME
----------
         5
 
ops$tkyte@ORA9IR2> exec dbms_job.run(:n)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select total_time from user_jobs where job = :n;
 
TOTAL_TIME
----------
        10
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_job.remove(:n)
 
PL/SQL procedure successfully completed.
 

select distinct sid from v$mystat;

create table t (x number);
insert into t values (1);
insert into t values (11);
commit;

create or replace procedure p_test
is
cursor c is select x from t where x <10 for update;
r c%rowtype;
begin
for r in c loop
update t set x=x+1 where x=r.x;
end loop;

commit;
end;

variable j number;
exec dbms_job.submit(job=>:j,what=>'p_test;',next_date=>sysdate+5/1440);

Ran it
-------------------------------------------------------
14:58:33 sql> select distinct sid from v$mystat;

SID
---------------
20

1 row selected.

Elapsed: 00:00:00.20
14:58:34 sql>
14:58:34 sql> create table t (x number);

Table created.

Elapsed: 00:00:00.60
14:58:34 sql> insert into t values (1);

1 row created.

Elapsed: 00:00:00.31
14:58:34 sql> insert into t values (11);

1 row created.

Elapsed: 00:00:00.10
14:58:34 sql> commit;

Commit complete.

14:59:17 sql> create or replace procedure p_test
14:59:54 2 is
14:59:54 3 cursor c is select x from t where x <10 for update;
14:59:54 4 r c%rowtype;
14:59:54 5 begin
14:59:54 6 for r in c loop
14:59:55 7 update t set x=x+1 where x=r.x;
14:59:55 8 end loop;
14:59:55 9
14:59:55 10 commit;
14:59:55 11 end;
14:59:55 12 /

Procedure created.


15:00:07 sql> exec dbms_job.submit(job=>:j,what=>'p_test;',next_date=>sysdate+5/1440);

PL/SQL procedure successfully completed.


15:06:08 sql> select * from v$lock where lmode=6;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
-------- -------- --------------- -- --------------- --------------- --------------- ---------------
BLOCK
---------------
9F64C78C 9F64C79C 3 RT 1 0 6 0 965802
0

01AF2F8C 01AF3058 20 TX 65543 2369 6 0 381
0


2 rows selected.

-------------------------------------------------------

I ran the script above in session 1.
When queried table t on session 2 it showed
15:11:20 rtbe@RTBE.CRONOS> select * from t;

X
---------------
1
11

2 rows selected.

Only when i exited Session 1 did i get.

15:15:24 rtbe@RTBE.CRONOS> select * from t;

X
---------------
2
11


Why is that so ?

Is it because
1. the FOR UPDATE Clause;
2. Commit statement

Which one is it ?

3.After i exited session 1 did it release the lock. Why is this so ?
Session 2 Query after exiting session 1
----------------------------------------
sql> select * from v$lock where lmode=6;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
-------- -------- --------------- -- --------------- --------------- --------------- ---------------
BLOCK
---------------
9F64C78C 9F64C79C 3 RT 1 0 6 0 966316
0

1 row selected.

4. Isnt it suppose to run when the next_date+next_sec passed not when i exited session 1 ??

5. Do i have to declare my procedure to be an 'autonomous transaction' ??

in my stored procedure can I determine wether I was invoked from a job or other ( ie command line ) ? I need to make sure that if a job is set up to run a stored procedure or is running that the same stored procedure cannot be invoked from sqlplus !

I cannot imagine ever getting in this situation in production as the job runs every 5 seconds and noone should be running the stored procedure. However testers proved it can cause problems if this happens.

any ideas ?

many thanks

ops$tkyte@ORA9IR2> create table t ( msg varchar2(40) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p
  2  as
  3          l_job user_jobs%rowtype;
  4  begin
  5          select * into l_job
  6            from user_jobs
  7           where job = sys_context( 'userenv', 'fg_job_id' );
  8
  9      insert into t values ( 'ok' );
 10      commit;
 11  end;
 12  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p
BEGIN p; END;
 
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 1
 
 
ops$tkyte@ORA9IR2> variable n number;
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'p;' ); commit;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_job.run(:n);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
MSG
----------------------------------------
ok
 
ops$tkyte@ORA9IR2>
 

Tom:

I have a code that schedules a job after insert into a table that alerts a user that no action was taken within two days. If any action was taken in the two days I delete the job.

The problem is that if the insert happened on "Friday" the job will run on Sunday.

I want to schedule friday jobs to run on Tuesday instead of sunday and thursday inserts to run on monday.

Would you use the decode statement to check for todays date and schedule it based on that?

dbms_job.submit(l_job,'SEND_EMAIL(JOB);',decode(to_char(sysdate,'DAY'),'FRIDAY',sysdate+4,'THURSDAY,'sysdate+3,sysdate+2);


I do not know why the decode here does not return the correct value for todays date. It should be M.

select decode(TO_CHAR (sysdate,'DAY'),'MONDAY','M','TUESDAY','T','F') from dual


D
-
F

1 row selected.

In 10g, If I used the "new" scheduler feature to schedule all of my jobs, do I still need to specify job_queue_processes parameter? Thanks.

So in 10g, if I used scheduler to manage all of the jobs, I don't have to set job_queue_processes parameter at all. right?

In 10g, using Database Control EM interface, If I exported using datapump, which job process (scheduler job process or dbms_job process or EM agent process) is used? Thanks.

If I have a job that runs every 10 minutes, would each execution of the job get its own Oracle 'session'? Or since it is run by the SNP background process, they share the same session?

It would matter for things like 'alter session' and if the job uses some package state.

Thanks

If I schedule a job to run every 10 minutes, assume that first time the job started at 00:00 and if the job itself takes about 15 minutes to complete, would the next interval be automatically reset in such a way that the job would start only at the 25th minute instead of @20? Thanks.

I need to execute a procedure 17.45 everyday. Here what I have done.

procedure payment_job is
l_job number;
begin
dbms_job.submit(l_job, 'payment.auto_generate;', trunc(sysdate) + 17/24 + 45/60/24, 'sysdate+1');
--jobs scheduled at 17.45 at each day
commit;
end;

I call "exec payment.payment_job;" from SQLPLUS.
When checked following day, the scheduled job wasn't executed.

So I tried following, insert a simple log statement to an error log table every 10 minutes.


declare
l_job number;
begin
dbms_job.submit(l_job, 'util.write2log(''Error Code'', ''Error Desc'');',sysdate+1/60/24, 'sysdate+11/60/2
commit;
end;
/



Unfortunately job didn't run as scheduled every 10 mins. I can run it using dbms_job.run(), but that is not I am after obviously!

(1)What could have gone wrong above? Where to look for errors? Note that both procedures run without a problem when invoked separately.

(2) Why Oracle has chosen an instance of a Date such as 'sysdate+1' instead of a time interval such as "interval day to second" OR "Number(in number of days)" OR "Number (in number of Seconds)" for the "interval" parameter in DBMS_JOB.Submit and other procs?
It is very confusing for me to think a specific 'time' as a 'time interval'. Please explain.

(1)job_queue_process was 0 and I set it to 10. How do I determine the best value for this parameter? I only have handlful 5-6 jobs running nightly. (9i R2)

(2) One * for this answer. I disagree with you Tom. No need to be a 21st centuray software to know the difference between time and time interval.

However I can pardon Oracle as docs explain that "interval" is simply used to calculate "next_date" using 'select interval into next_date from dual'. But if I was the programmer I would have named that parameter anything but 'interval'. e.g: new_next_date

Thank you for your answer. Its really helps.

hi tom,

if we export whole database via export dump.. will the job created be exported and will it run successfully at the place where it is imported...

I did the same, but job did not start at the scheduled time

Thanks in Advance

Sanjay

It is written that next run date is calculated just before execution of job. If this is case then if a job takes 10 minutes to execute and job interval is 2 minutes then job overlapping is possible ? But I think because of JQ lock overlapping is not possible. To prevent job overlapping , next run date needs to be calculated at the end. So which one is correct ? IF overlapping does not happen and next run date is also calculated at begining then how the above mentioned case will be handled ?

Deba

and FAST_SEND_MAIL.

I don't see a commit on page 1065 or 1066 to get that job pumping.


Am I missing it or is it missing?

Thanks,

Justin

The examples are really very appealing and clears our doubts very easily

Hi Tom,
I have diffrent Package , Procedure & Function with the following name for example:

(1)create or replace package lob_client_operations as

function get_lob_size(p_tablename in varchar2,
p_pkcolname in varchar2,
p_textcolname in varchar2,
p_docid in varchar2) return number;

procedure get_lob_chunk ( p_tablename in varchar2,
p_pkcolname in varchar2,
p_textcolname in varchar2,
p_docid in varchar2,
amount IN OUT NUMBER,
offset IN NUMBER,
buff OUT RAW);


procedure insert_new_record ( p_tablename in varchar2,
p_pkcolname in varchar2,
p_textcolname in varchar2,
p_docid in varchar2);

end;
/
-- ---------------------------- ---
-- ---------------------------- ---

(2)
(a)CREATE or REPLACE PROCEDURE SimpleTextMessage IS
(b) create or replace procedure html_email( p_to in varchar2, p_from in varchar2, p_subject in varchar2,
p_text in varchar2 default null) is
-- ---------------------------- ---
-- ---------------------------- ---

(3) create or replace function send(
p_from in varchar2,
p_to in varchar2,
p_subject in varchar2,
p_body in varchar2) return number as

-- ---------------------------- ---
-- ---------------------------- ---

I want to execute the above thing on every "Wednesday" at different time (1) Package on time 3:00 A.M, (2) Procedures on time 4:00 A.M & (3) Function on time 4:30 a.m.

How I can execute it can you give your valuable point with example:

Thanks in Advance

Tom:

Do you know what may cause this? There is nothing wrong with the procedure.  Could it be with the package itself.

SQL> execute dbms_job.run(1966);
BEGIN dbms_job.run(1966); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 405
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at line 1 

hi tom
I have written some dbms job to perform some task . i am able to do the task manually ie by running the dbms job it performing the task . i have written GUI in jsp to perform same task. I am not able to perform the same task from GUI i am fresher. would u PLZ tell how to call call dbms job from using GUI in JSP or JAVA hope i get what i want .
Thanks in adv .

What is the best way to stop a job that is running on a
infinite loop (runs long time ).

dbms_job.broken (jno, true);
dbms_job.broken(jno, false, next_date) does not stop

Is dbms_job.remove and dbms_job.submit is the only way

Also I like to know what happens when I kill the SID and
the associated SPID from os. Because job goes away from
the queue momontarily, but starts back again

If the job has been submitted by a user, "scott"
can the user with DBA role (for instance SYS) alter
the jobs in the queue submitted by "scott" or only
"scott" can execute dbms_job.<any procedure> on it

You have to be in as the owner of the job to manipulate it.

ops$tkyte@ORA10GR1> variable n number
ops$tkyte@ORA10GR1> exec dbms_job.submit( :n, 'null;', sysdate+1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1> commit;
 
Commit complete.
 
ops$tkyte@ORA10GR1> print n
 
         N
----------
        44
 
ops$tkyte@ORA10GR1> connect / as sysdba
Connected.
sys@ORA10GR1> exec dbms_job.remove( 44 );
BEGIN dbms_job.remove( 44 ); END;
 
*
ERROR at line 1:
ORA-23421: job number 44 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 171
ORA-06512: at line 1
 
 
sys@ORA10GR1> connect /
Connected.
ops$tkyte@ORA10GR1> exec dbms_job.remove( 44 );
 
PL/SQL procedure successfully completed.
 
 

what will be effect if just

set the job queue processes to 0

Will it stop the running job process

As a continuation to previous posting,

when I did a test
1.with "what" parameter = a procedure which calls
dbms_lock.sleep(10000)
2. submit job
3. select * from dba_jobs_running; Note the SID
3. alter system set job_queue_processes =0
4. select spid from v$process
where v$session.sid= <value from step 3.>
5. ps -ef|grep <value from 4.>

I still see the J___ process
and I still see the dba_jobs_running show the job
as running

Am I right assuming that setting job_queue_processes to 0
does not stop the job, but let run until completion

Hi,

I have 4 jobs scheduled to run every night in a database and instead of manually logging into the database to check the status of each job, is there a way that I can have the results of the job emailed to me automatically every morning?

Thanks.

The job was supposed to run 10000 secs (2.7 hours)
However after setting job_queue_processes to 0
The job finished in less than an hour. Could you
let us know why. May some periodic polling of some sort

select text from dba_source where name = 'TESTQ';
 
TEXT
--------------------------------------------------------------------------------
procedure testq
as
 begin
sys.dbms_lock.sleep(10000);
end;



09:23:27 SQL> var num number;
09:23:54 SQL> exec dbms_job.submit(:num,'testq;',sysdate,'sysdate+1');
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
09:24:03 SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00.02
09:24:15 SQL> select sid,job,this_date,this_sec from dba_jobs_running;
 
       SID        JOB THIS_DATE THIS_SEC
---------- ---------- --------- --------
        17         43 08-MAR-05 09:24:16
 
Elapsed: 00:00:00.02
09:24:32 SQL> alter system set job_queue_processes = 0;
 
System altered.
 
Elapsed: 00:00:00.03
09:24:54 SQL> sho parameter job_queue_processes
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
09:25:03 SQL> select sid,job,this_date,this_sec from dba_jobs_running;
 
       SID        JOB THIS_DATE THIS_SEC
---------- ---------- --------- --------
        17         43 08-MAR-05 09:24:16
 
Elapsed: 00:00:00.02

09:25:37 SQL> select sid,job,this_date,this_sec from dba_jobs_running;
 
       SID        JOB THIS_DATE THIS_SEC
---------- ---------- --------- --------
        17         43 08-MAR-05 09:24:16
 
Elapsed: 00:00:00.02
09:25:54 SQL> select sid,job,this_date,this_sec from dba_jobs_running;
 
       SID        JOB THIS_DATE THIS_SEC
---------- ---------- --------- --------
        17         43 08-MAR-05 09:24:16
 
Elapsed: 00:00:00.02
09:49:31 SQL>     
09:52:47 SQL> select sid,job,this_date,this_sec from dba_jobs_running;
 
no rows selected
 
Elapsed: 00:00:00.02
10:11:47 SQL> 
 

Yes. I did not remove the job from the queue

My point is, why would the job be incompletely stopped.
Would it not be run until it is completes.

Could you give a counter example that shows the
job will run to its completion even when the
process is set to 0

I am thinking if smon might be checking periodically
the resources available and stopped the run. Is this possible. If so, how often smon checks

I do  not think it is in the queue:

SQL> select job, log_user,lasT_date, last_sec, this_date,this_sec,next_date,next_sec from user_jobs
  2  where job = 43;
 
       JOB LOG_USER                       LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
---------- ------------------------------ --------- -------- --------- -------- --------- --------
        43 TEST                           08-MAR-05 09:24:16                    09-MAR-05 09:24:16
 

Thanks very much

Can i record the failed jobs(dbms_job) into a table?

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin TEXT_REBUILD; exception when others
then
sec_pkg.InsertLog(''JOB FAILURE'',''TEXT REBUILD JOB FAILED'',''2'',''job'');
commit;
RAISE;
END;'
,next_date => to_date('15/03/2005 03:46:30','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+1/720'
,no_parse => TRUE
);
END;
/



Here is the text rebuild package

CREATE OR REPLACE PROCEDURE TEXT_REBUILD
AS
CURSOR CUR IS SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_TYPE='DOMAIN';
IND_NAME VARCHAR2(1024);
BEGIN
OPEN CUR;
LOOP
FETCH cur INTO IND_NAME;
EXIT WHEN cur%NOTFOUND;
ctx_ddl.sync_index(''||IND_NAME||'');
END LOOP ;
CLOSE CUR;
END TEXT_REBUILD;


sec _pkg:
CREATE OR REPLACE PACKAGE BODY sec_pkg
AS

PROCEDURE InsertLog(
p_log_type IN VARCHAR2,
p_log_desc IN VARCHAR2,
p_log_user_id IN VARCHAR2,
p_log_ip IN VARCHAR2

)
AS
log_id NUMBER;

BEGIN

SELECT sec_seq.NEXTVAL
INTO log_id
FROM DUAL;

INSERT INTO SEC_LOGS
(
ID, LOG_TYPE,
LOG_DESC, LOG_TIMESTAMP,
LOG_USER_ID,LOG_IP
)
VALUES
(
log_id , p_log_type, p_log_desc,
to_date(to_char(SYS_EXTRACT_UTC(sysTIMESTAMP),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi'),
p_log_user_id, p_log_ip
);


END InsertLog;

END sec_pkg;
/



When I am executing the jobs manually using exec job its not throwing no errors. But when executing the jobs as per the schedule time, some times jobs are failing by throwing the following errors in the trace files-
ORA-12012: error on auto execute of job 241
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

Can you please look into this error and suggest the solution?

CURSOR CUR IS SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_TYPE='DOMAIN'
This is the only select statement in the procedure.
When i am executing explicitly its not throwing any error. The job is not failing every time.
Please help me in solving the issue.

The trace file messages are misleading. There is nothing wrong with your code. You can try a workaround until someone figures out what is the real problem

create procedure workaround_proc(jb integer) as
j integer;
begin
dbms_job.remove(jb);
begin
text_rebuild;
exception when others
then
sec_pkg.InsertLog('JOB FAILURE','TEXT REBUILD JOB
FAILED','2','job');
end;
dbms_job.submit(j,'workaround_proc(job)',sysdate+1/720);
commit;
end;

By the way unhandled exceptions inside a job cause Oracle to reexecute the job - is that your real aim

The error is not in the "what" part of the job. If the "what" is executed standalone everything is ok. If it is submitted as
a job then the error occurs. I've got the same error for 6
different jobs running in one database. The same jobs run ok in other databases. I tried a simple job like

dbms_job.submit (j,'insert into table a values(1);commit;',...);

and got the same error at line 5. There is no line 5 in this job, is there? And the row was inserted.

i thought you won't believe me, so I submitted a very simple job
SQL> declare
  2  j integer;
  3  begin
  4  dbms_job.submit(j,'null;',sysdate+1/720);
  5  commit;
  6  dbms_output.put_line(to_char(j));
  7  end;
  8  /
501

PL/SQL procedure successfully completed.

SQL> select job,this_date,this_sec,last_date,last_sec,next_date,next_sec,
  2  total_time,broken,interval,failures,what
  3  from user_jobs where job=501;


      JOB THIS_DATE THIS_SEC LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
--------- --------- -------- --------- -------- --------- -------- ---------- -
INTERVAL
-------------------------------------------------------------------------------------------
 FAILURES
---------
WHAT
-------------------------------------------------------------------------------------------
      501                                       17-MAR-05 09:06:07          0 N
null

null;


SQL> /

      JOB THIS_DATE THIS_SEC LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
--------- --------- -------- --------- -------- --------- -------- ---------- -
INTERVAL
-------------------------------------------------------------------------------------------
 FAILURES
---------
WHAT
-------------------------------------------------------------------------------------------
      501                                       17-MAR-05 09:08:23          1 N
null
        1
null;


and here is an excerpt from the trace file

*** SESSION ID:(139.64555) 2005-03-17 09:06:23.119
*** 2005-03-17 09:06:23.119
ORA-12012: error on auto execute of job 501
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 5
*** SESSION ID:(139.64563) 2005-03-17 09:08:26.051
*** 2005-03-17 09:08:26.051
ORA-12012: error on auto execute of job 501
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 5

I'm not quite sure what you are proposing to trace. Do you mean 

dbms_job.submit(j,'alter session set sql_trace=true;null;',sysdate+1/720);

 

Hi TOm,

I have a job that should execute everyday with an interval of every 15 minutes and it should not execute between 17:30 - 19:00 Hours. Pls. throw light on how to set the time restriction between 17:30 - 19:00 Hrs only on Sundays.

a. To Execute every 15 mintues on a daily basis.

'SYSDATE+15/1440'

b. Not to execute on Friday between 17:30 - 19:00 Hrs?

?????????????


Please advice

Regards
Sree

Dear Tom,

Env: Oracle 8.1.7 , 2 node OPS
I thought all the DBA_ views are global(will display all nodes information, similar to gv$ views) in OPS/RAC environment. 

But the thing below I see here is different.Is it an expetced behavior?

INST1
=====
SQL> select * from dba_jobs_running;
no rows selected

INST2
=====
SQL> select * from dba_jobs_running;

      SID        JOB   FAILURES LAST_DATE         LAST_SEC
---------- ---------- ---------- ----------------- ------------------------
THIS_DATE         THIS_SEC                   INSTANCE
----------------- ------------------------ ----------
       10        479          0 02/21/05 12:23:57 12:23:57
03/07/05 12:23:58 12:23:58                          0 

Dear Tom,

Thanks for your response.

Env: 8.1.7 OPS, Solaris

Job#479 is not running as scheduled(not even manually) because the job is locked by SID 10. I killed SID 10 yesterday but still the lock is not released. Also there is no OS process associated with this SID.

How to clear this lock, so that the job will run automatically as scheduled.


SQL> SELECT sid, type, id1, id2
FROM v$lock
WHERE type = 'JQ';  2    3  
 
       SID TY        ID1        ID2
---------- -- ---------- ----------
        10 JQ          0        479
 
SQL> select sid,serial#,status,last_call_et,logon_time,process from v$session where sid=10;
 
       SID    SERIAL# STATUS   LAST_CALL_ET LOGON_TIME        PROCESS
---------- ---------- -------- ------------ ----------------- ---------
        10        163 KILLED        1203947 03/07/05 12:23:58
 
SQL> /
 
       SID    SERIAL# STATUS   LAST_CALL_ET LOGON_TIME        PROCESS
---------- ---------- -------- ------------ ----------------- ---------
        10        163 KILLED        1203959 03/07/05 12:23:58
 
 

SQL> l
  1* select * from v$session_wait where sid=10
SQL> /
 
       SID       SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------------------------- ---------- -------- ---------- ---------------
STATE
-------------------
        10      16405 enqueue
name|mode                                                        1347616774 50530006
id1                                                                       1 00000001
id2                                                                       1 00000001          0           35397
WAITING
 
 

as far as my jobs are concerned the mysterious line 5 was due to a logoff trigger rewritten from </code> http://www.dbazine.com/burleson7.shtml <code>
which looks like this
BEGIN
--*************************************************
--Update the last action accessed
--*************************************************
/*my comment - line 5 follows*/
update stats_user_log set last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
..........

May I quote
"There is no "audsid" for the job" from your followup in the thread "getting my row from v$session".
So this is the reason for the error on line 5 and it has nothing to do with the job or the job mechanism. May be one should not use audsid in logon/logoff triggers

If we are using your trick ..

begin
dbms_job.submit( l_job, 'P(job);', ..... );
insert into job_parameters( l_job, ..... );
commit;
end;

How we are sure that there is record in job_parameters when procedure P starts ?????
How could we prove ourselves that insert will 100% happened before job runs procedure P.
(knowing that insert follows submit in code).

Thanks.

Yes, I know as transaction both fail or both succed.
But anyway we coded
1. submit (which runs procedure p in another session )
and p wants to read record which will be
inserted later
2. insert ...
--
My question was, how happened, that record inserted in second step is allready there, when procedure p submited in earlier step wants to read it.

Regards.

Yes, (sorry but I wan't to understand ..)
--
When I commited
- job is submitted, run and p is executed in another session
- insert is committed in my session

If nothing goes wrong, is there any possibility that establishing new session and executing p will be faster than insert will be commited and exposed to other sessions ?

I thought I knew all there is to know as far as dbms_job goes in all releases between 8i, 9i and 10g.

I have written a simple PL/SQL procedure that assembles DDL statements to drop synonyms and create a new set based on the contents at a permanent control table. User may set a set-id of her choosing and job gets sumitted. This PL/SQL procedure runs with no errors on its own.

However when submitted (one-off job) in Oracle 8.1.7 it fails with ORA-01422. And then with identical set up when runs under 9.2.0.5 - NO PROBLEM.

Is there a RDBMS bug in 8.1.7 ...?

Thanks Tom

Tom thank you.

Below is the relevant excerpt of the procedures and errors captured:

Create or replace Procedure CWESPROD.XDL_TST03 (act_id in number)
As
Xdl varchar2(200);
Xsch varchar2(30);
Xsn varchar2(30);
Xls varchar2(30);
Xlt varchar2(30);
Xdblnk varchar2(30);
--
Xln number :=0;
Xscd number :=0;
Xsem varchar2(80);
Begin
Xln :=1;
XDL := ‘Insert into SYSTEM.XDL_TRC Select sysdate from dual’;
EXECUTE Immediate XDL;
Insert into SYSTEM.XDL_TRC Values (‘Step #1 = OK.’);

XLN :=2;
XDL := ‘Drop table XZ1’;
EXECUTE Immediate XDL;
Insert into SYSTEM.XDL_TRC Values (‘Step #2 = OK.’);

... more test code - not executed ...
EXCEPTION
When others then
Xscd := SQLCODE;
Xsem := substr(SQLERRM,1,80);
Insert into SYSTEM.XDL_TRC Values (‘***ERROR***’);
Insert into SYSTEM.XDL_TRC Values (‘Line=’||xln);
Insert into SYSTEM.XDL_TRC Values (‘Err.code=’||xscd);
Insert into SYSTEM.XDL_TRC Values (‘Err.msg =’||xsem);
End;
/
Create or replace procedure CWESPROD.XDL_TST01_JOB
As
XJN number :=0;
PRM number :=3;
ZPRC varchar2(100);
Begin
ZPRC := ‘Begin CWESPROD.XDL_TST03 (‘||PRM||’); END;’;

Dbms_job.submit (XJN, ZPRC);
Insert into SYSTEM.XDL_TRC Select ‘Job # ‘||XJN||’ at: ‘||
to_char(sysdate,’yyyymmdd hh24:mi:ss’) from dual;
End;
/
<<< ERRORS stored in XDL_TRC table >>>
XDL
----------------------------------------------------------------------------
Job # 19 at: 20050511 10:14:54
11-MAY-05
Step #1 = OK.
***ERROR***
Line=2
Err.code=-1422
Err.msg =ORA-01422: exact fetch returns more than requested number of rows

<<<< NOTE 1: >>>>
The procedure XDL_TST03 executes OK when run on its own but fails when executed from Oracle NT 8.1.7 dbms_job as:
begin XDL_TST01_JOB; END;
/
commit;

When the same code is compiled and executed on 9.2.0.5 NT platform - all steps execute OK just like the Step #1

<< PLEASE IGNORE PARAMETER ACT_ID - it is NOT USED HERE >>

<<<< NOTE 2: >>>>
When EXCEPTION Section is commented out the relevant error 1422 is recorded in the Alert and trace files and point to the line with EXECUTE IMMEDIATE - same section i.e. that starts with XLN := 2...

<<<< NOTE 3: >>>>
I tried several other individual DDL statements like CTAS or Create Synonym etc., and ALL fail with 1422 ERROR but only on NT 8.1.7 release and NOT on 9.2.0.5

I was tempted to test DBMS_SQL - but I thought I would share my experiences with you.

This is the very first time that I am bringing an issue at your site (I have visited your column and read your books and papers with great interest, though).

I have managed to resolve hundreds of Oracle problems in my 16+ years with Ortacle (5.1.B through 10g) by myself -
but this time I thought you might like to hear of this peculiar ERROR - or..? I am missing something...? Hopefully not.


WHAT OTHER INFORMATION WOULD YOU LIKE ME TO PROVIDE..?

Best regards

Andre

PS

BTW.
You mat recall - we met during HOTSOS 2004 symposium...
- Now I am in Ireland /the UK - and keep consulting in the Insurance sector (Hibernian, Marsh UK and now Eagle Star)

Best regards from this Green Island
AS

Dear Tom,

Your input has proved to be 100% CORRECT.
THANK YOU.


Tom, you said:
"In fact -- I'll bet it is a DDL trigger on drop..."

INDEED! IT IS AN "AFTER DDL" TRIGGER THAT CAUSED ERRORS

My lesson (and caution to others)
=> NEVER ENTER A BET WITH YOU <=


I know that it is a rather poor excuse - however, I may mention this as a warning to other fellow consultants:

>>> BEFORE RAISING AN ERROR FLAG WITH TOM
>>> CHECK CAREFULLY YOUR DATABASE ENVIRONMENT
>>> ESPECIALLY FOR TRIGGERS.
>>> ... then for other things - thorough research.!
>>>
>>> INDEED THERE WAS NOTHING WRONG WITH 8.1.7
>>> (See my former comments above)
>>> ONLY the FORMER DBA CODED AUDIT TRIGGERS ON
>>> ALL DATABASES (PROD OR DEVELOPMENT)
>>> AND...
>>> HE HAD NOT DONE SO CORRECTLY.
>>> And - they only now implemented 9iR2..!!!
>>> It is a 7.3.3.6 + 8.1.7 IT GROUP in May 2005
>>> with only a couple of 9i environments.
>>>
>>> ALSO:
>>>
>>> NOW - I ALSO UNDERSTAND AS TO WHY THEY NEVER
>>> USED DBMS_JOB - BUT RESORTED TO CRON & AT
>>>
>>> WELL, I GOT CAUGHT HERE...
>>> ... AND I THOUGHT THAT I HAVE BEEN CAREFUL
>>> IN MY TESTS.
>>>
>>> WILL TRY TO DO MUCH BETTER NEXT TIME.

THANKS AGAIN TOM.


Regards,
Andre

PS.
I normally do not resort to SYSTEM.
Here I have resorted to several tests on this DEV DB
and used different schemas including SYSTEM.
Please forgive me for that.
AS

Re: The fast_send_mail code listing on page 1065 of One-on-One:

Am I right in assuming that both l_job and JOB have the same value?

I am not 100% sure about how *undeclared* parameters are accessed within PL/SQL, and in particular, if (hypothetically) DBMS_JOB had passed 2 parameters (or 3, 4, etc.), then how would the 2nd, 3rd, etc. parameters be accessed within the PL/SQL code? Are they assigned, in sequence, to the variables following the *as*?

Hi Tom,

I have a situtaion after I submitted few database jobs in one instance of RAC. Our application has the capability to login either node ( call whatever either node balancing etc). Few of these jobs process some information and data via usage of DBMS_ALERTS and DBMS_PIPES.

Now the situation with our datbase jobs found in this two node RAC environment is, if any application logged in via node 2 is unable to get process those DBMS_ALERT signals data that has been started from Node 1 as an dbms job and then loss of data happening. Also if I go and check the current jobs running status through dba_jobs_running, it only shows from the node 1 database if I login . If I login through node 2 database then the jobs running status is always 0 or nothing. How to get this to be resolved. My application works great in singlenode instances and we recently perform this on RAC (two node ) and if every one logs through node 1 then everything works fine as I started those jobs from Node 1. But if any one logs from Node2 and then jobs started fron node 1 then troubles started. Can you suggest how to fix this such situations?.

I try to submitt the same job from the second node and it didn't do anything ( since the job submitted from the first node runs for every 5 seconds and shows from node 1 fine). If I stop all from first node and start from 2 node then the application whoever login from node 1 get lost that data dur to lack of that job started from node 1 and Node 2 people application is fine. Help please !!1

Hi Tom,

Thanks for the quick reply. Yes,DBMS_JOB is sure RAC friendly, but one of the job uses those DMBS_ALERT and DBMS_PIPES and so the reason we are unable to collect those information that comes through pipes especially those information comes form DBMS_ALERT signals that we coded in the job. Due to this single "one and only" reason our entire product failing on the RAC. My only ambition here is looking for some clues or helphints, how we can make those DBMS_PIPES to have RAC compliant. If I share my code any hint or any help !!!?.

Our application needs most of time this non stop job that is throwing those information through alerts and collected by DBMS_PIPES. If I start the same job through another node, it obviously wouldn't start due to the reaso that job already running from another node in the database.. any help please !!

Tom,

Tones of thanks for qucik reply. Can I get a piece of small exmaple to use this instance parameter to say that pipe to use that instance though it runs from another instance. Say for e.g I am using inst1 and inst2 are two instances running from two different nodes and my job started running on inst1 and the few users logged from inst2 and unalbe to get that information. I know I need to submitt before you "I am not aware of this kind of instance parameter usage to tell that DBMS_PIPES to go and get from inst1" Just a little code snippet would help for me to come out my entire dilema ,,, thanks for your help indeed !!

  1* select instance_number, instance_name from gv$instance
SQL> /
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 O10G1
              2 O10G2
 


just pass ...., INSTANCE => :x );  to the submit call, with the instance number of the one you want. 

Tom,

Not yet really got clues how to resolve. Here is my code of that job called auto logging and this is the one used by our application from so many corners for any kid of logging activity and this writes any error into our log tables and this we started as an dbms_job to run almost non stop (every 2 secs ). Didn't understand how to incorporate your instance number (no matter users login whatever node they want ) :-

--------- my code -----------------------------
create or replace PROCEDURE AUTO_Logging
is

c_site_len constant number(4,0) := 4;
c_user_id_len constant number(4,0) := 9;
c_app_id_len constant number(4,0) := 16;
c_module_id_len constant number(4,0) := 255;
c_wkstn_id_len constant number(4,0) := 5;
c_wkstn_name_len constant number(4,0) := 15;
c_session_id_len constant number(4,0) := 8;
c_log_type_len constant number(4,0) := 1;
c_log_text_len constant number(4,0) := 2000;
v_site X_logging.site%type;
v_user_id X_logging.user_id%type;
v_application_id X_logging.application_id%type;
v_module_id X_logging.module_id%type;
v_module_255 varchar2(255);
v_text varchar2(32767);
v_workstation_id X_logging.workstation_id%type;
v_session_id X_logging.db_session_id%type;
v_workstation_name X_logging.workstation_name%type;
v_log_type X_logging.log_type%type;
v_log_text X_logging.log_text%type;
v_log_date varchar2(20);

--local values
v_pipe_name varchar2(255);
v_max_pipe_size number(9);
v_result X_col_types.number_9%type;
v_result_msg X_col_types.varchar_255%type;
v_pipe_open char(1);
v_time_out_seconds number(9);
v_err number(9);
--
function get_next_field
return varchar2
is
c_no_more_items constant integer := 0;
c_varchar2 constant integer := 9;
c_number constant integer := 6;
c_date constant integer := 12;
v_text varchar2(32767);
v_number number;
v_date date;
v_next_type integer;
v_return varchar2(32767);
begin
v_next_type := dbms_pipe.next_item_type;
if v_next_type = c_no_more_items then
v_return := null;
elsif v_next_type = c_varchar2 then
dbms_pipe.unpack_message(v_text);
v_return := substr(ltrim(rtrim(v_text)), 1, 2000);
elsif v_next_type = c_number then
dbms_pipe.unpack_message(v_number);
v_return := '' || to_char(v_number);
elsif v_next_type = c_date then
dbms_pipe.unpack_message(v_date);
v_return := '' || to_char(v_date, X_date_format);
else
v_return := null;
end if;
return v_return;
exception
when others then
v_return := 'ERROR ' || sqlcode;
return v_return;
end;
--
function make_number (
p_in in varchar2)
return number
is
begin
return to_number(p_in);
exception
when others then
return 0;
end;
--
-- common log proc
--
procedure logit (
log_date X_logging.log_date%type,
site X_logging.site%type,
user_id X_logging.user_id%type,
application_id X_logging.application_id%type,
module_id X_logging.module_id%type,
workstation_id X_logging.workstation_id%type,
workstation_name X_logging.workstation_name%type,
db_session_id X_logging.db_session_id%type,
log_type X_logging.log_type%type,
log_text X_logging.log_text%type
)
is
begin
insert into X_logging
(
log_number,
log_date,
site,
user_id,
application_id,
module_id,
workstation_id,
workstation_name,
db_session_id,
log_type,
log_text
)
values (
X_log_number_sequence.NEXTVAL,
log_date,
site,
user_id,
application_id,
module_id,
workstation_id,
workstation_name,
db_session_id,
log_type,
log_text
);
-- commit logging trasaction
commit work;
exception
when dup_val_on_index then
--
-- uh-oh, sequence got reset, fix value
--
ep_fix_sequence('X_LOG_NUMBER_SEQUENCE', 'X_LOGGING', 'LOG_NUMBER', v_result, v_result_msg);
--
-- now, try again
--
insert into X_logging
(
log_number,
log_date,
site,
user_id,
application_id,
module_id,
workstation_id,
workstation_name,
db_session_id,
log_type,
log_text
)
values (
X_log_number_sequence.NEXTVAL,
log_date,
site,
user_id,
application_id,
module_id,
workstation_id,
workstation_name,
db_session_id,
log_type,
log_text
);
-- commit logging trasaction
commit work;
end;
--
--
-- main procedure start
--
--
begin

v_err := 0;
v_result := 0;
v_result_msg := null;
v_pipe_open := 'N';
v_session_id := ltrim(rtrim(substr(userenv('SESSIONID'),1,8)));
if length(v_session_id) = 0 or v_session_id is null then
v_session_id := 'UNDEFINED';
end if;
v_workstation_name := ltrim(rtrim(substr(userenv('TERMINAL'), 1, 8)));
if length(v_workstation_name) = 0 OR v_workstation_name is null then
v_workstation_name := 'UNDEFINED';
end if;
v_log_date := Xdatenow;
-- record startup

logit(
v_log_date, /*'log_date_t_char20'*/
'XIC', /*'site_t_char4'*/
0, /*user_id_t_numeric*/
'XIOMS', /*'application_id_t_varchar'*/
'XAUTO_LOGGING', /*'module_id_t_varchar'*/
0, /*'ws id'*/
v_workstation_name, /*'tcpip_address_t_varchar'*/
v_session_id, /*'db_session_id_t_varchar'*/
'S', /*'log_type_t_char1'*/
'Logging started' /*'log_text_t_longvarchar'*/
);
--
v_pipe_name := 'XIOMS$LOG_INFO_PIPE';
v_max_pipe_size := 1000000; --bytes
v_time_out_seconds := 60;
--remove the pipe if it exists, and then recreate the pipe
v_result := dbms_pipe.remove_pipe(v_pipe_name);
v_result := dbms_pipe.create_pipe(v_pipe_name, v_max_pipe_size, false);
while true
loop
--start internal execution area so that any errors
--will be trapped here and not cause this routine to crash
begin
--if v_result = 0 or v_result = 23322 then
--pipe was created or already existed
v_result := 0;
v_err := 1;
v_result := dbms_pipe.receive_message(v_pipe_name, v_time_out_seconds);

if v_result = 0 then
--message was received, unpack it and store it in database
v_log_date := epicdatenow;

v_err := 2;

v_site := rtrim(ltrim(substr(get_next_field, 1, c_site_len)));
if upper(v_site) = 'STOP' then
--give a way to stop this process from outside
return;
end if;
if length(v_site) < 1 then
v_site := 'EPIC';
end if;
v_err := 3;

v_user_id := make_number(ltrim(rtrim(substr(get_next_field, 1, c_user_id_len))));
v_err := 4;

v_application_id := ltrim(rtrim(substr(get_next_field, 1, c_app_id_len)));
v_err := 5;

v_module_id := ltrim(rtrim(substr(get_next_field, 1, c_module_id_len))); -- 16);
if length(v_module_id) = 0 or v_module_id is null then
v_module_id := 'UNDEFINED';
end if;
v_err := 6;

v_workstation_id := make_number(ltrim(rtrim(substr(get_next_field, 1, c_wkstn_id_len))));
v_err := 7;

v_log_type := ltrim(rtrim(substr(get_next_field, 1, c_log_type_len))); -- 1);
if length(v_log_type) = 0 or v_log_type is null then
v_log_type := 'M';
end if;
v_err := 8;

v_log_text := ltrim(rtrim(substr(get_next_field, 1, c_log_text_len)));
v_err := 9;

v_session_id := ltrim(rtrim(substr(get_next_field, 1, c_session_id_len)));
if length(v_session_id) = 0 or v_session_id is null then
v_session_id := 'UNDEFINED';
end if;
v_err := 10;

v_workstation_name := ltrim(rtrim(substr(get_next_field, 1, c_wkstn_name_len)));
if length(v_workstation_name) = 0 or v_workstation_name is null then
v_workstation_name := 'UNDEFINED';
end if;
v_err := 11;
logit (
v_log_date, /*'log_date_t_char20'*/
v_site, /*'site_t_char4'*/
v_user_id, /*user_id_t_numeric*/
v_application_id, /*'application_id_t_varchar'*/
v_module_id, /*'module_id_t_varchar'*/
v_workstation_id, /*'mac_address_t_varchar'*/
v_workstation_name, /*'tcpip_address_t_varchar'*/
v_session_id, /*'db_session_id_t_varchar'*/
v_log_type, /*'log_type_t_char1'*/
v_log_text /*'log_text_t_longvarchar'*/
);
else
v_result := 0;
end if;
exception
when others then
v_result := sqlcode ;
v_result_msg := substr(sqlerrm,1,255);
begin
-- try to record in logging table - hope it was a bad input error
logit (
v_log_date, /*'log_date_T_CHAR20'*/
'XIC', /*'site_T_CHAR4'*/
-1, /*user_id_T_NUMERIC*/
'LOGGING', /*'application_id_T_VARCHAR'*/
'LOGGING', /*'module_id_T_VARCHAR'*/
0, /*workstation_id_T_SMALLINT*/
'ERROR', /*'workstation_name_T_VARCHAR'*/
'ERROR', /*'db_session_id_T_VARCHAR'*/
'E', /*'log_type_T_CHAR1'*/
substr('(' || v_err || ') ' || v_result_msg || ' [' ||
v_application_id || ',' || v_module_id || ']', 1, c_log_text_len) /*'log_text_T_VARCHAR'*/
);
exception
when others then
-- uh-oh,
return;
end;

end;
end loop;
exception
when others then
v_result := sqlcode ;
v_result_msg := substr(sqlerrm,1,255);

begin
-- try to record in logging table - hope it was a bad input error
logit (
v_log_date, /*'log_date_T_CHAR20'*/
'XIC', /*'site_T_CHAR4'*/
-1, /*user_id_T_NUMERIC*/
'LOGGING', /*'application_id_T_VARCHAR'*/
'LOGGING', /*'module_id_T_VARCHAR'*/
0, /*workstation_id_T_SMALLINT*/
'ERROR', /*'workstation_name_T_VARCHAR'*/
'ERROR', /*'db_session_id_T_VARCHAR'*/
'E', /*'log_type_T_CHAR1'*/
substr('(' || v_err || ')2 ' || v_result_msg || ' [' ||
v_application_id || ',' || v_module_id || ']', 1, c_log_text_len) /*'log_text_T_VARCHAR'*/
);
exception
when others then
-- uh-oh,
return;
end;
return;
END;
/

------ end of my code for auto logging -----------

Hi Tom,

Yes I did the same to run this code on both machines to avoid the conflicts of all. What I did is I made a simple change in the name of the procedure like one instance runs as auto_loggin and another instance runs auto_logging_RAC. Here the code is same on both, due to the reason I can't run the same name of procedure through DBMS_JOB non-stop in both the instances, I just changed the names of procedures and submitted those jobs from different instances and everything went well. Thanks for your valuable inputs especially letting me know that DBMS_PIPE is instant based.

Excellent and so valuable inputs from you and aprreciated

Hi Tom,

Though I figured out the way to run successfully these jobs on both the nodes of RAC, I hit with another issue. I couldn't get it resolved whatever brainstormed to my situation. My concern is if I want to start two different jobs ( say Job1 and Job2 ) one in node1 and one in Node2, how this is achievable in RAC environment?. If I submitt through DBMS_Job.submitt after connecting each node separately, then also there is no gurantee these jobs should go and sit in particular node. Some time the jobs submitted working from another Node (though I am connected and submitted in one particualr node). Is there any way I can restrict to submitt "The Job" into 'The particular Node".

Note:- In my situation I need to submitt jobs on both the nodes, so there is no way I can setup that job_que_process parameter to 0 on either of node.

Thanks

Hello Mr. TOm,

I've submitted a job to drop the user A after one minute:

declare
    jn binary_integer;
    the_text varchar2(200);
begin
    the_text := 'dbms_utility.EXEC_DDL_STATEMENT( ';
    the_text :=  the_text || chr(39) || 'drop user a cascade';
    the_text :=  the_text || chr(39) || ');';
    dbms_job.SUBMIT(jn, the_text, sysdate + 1/1440);
    dbms_output.put_line(jn);
commit;
end;

SQL> /
23

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

My init.ora file has following parameters set.

job_queue_processes = 2
job_queue_interval = 60
job_queue_keep_connections = false

But my job never runs. Can you tell me how to do this?

thanks
 

I am working in Oracle 8.0.5 & I have the following situation

SQL> select * from user_jobs;

no rows selected

SQL> declare
  2  j number;
  3  begin
  4  dbms_job.submit(j,'insert into emp(empno,ename)values(1,''hash'');',
  5  sysdate+1/1440);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select failures,interval,to_char(next_date,'dd/mm/yyyy hh24:mi:ss')
  2  from user_jobs;

  FAILURES INTERVAL TO_CHAR(NEXT_DATE,'
---------- -------- -------------------
         2 null     21/06/2005 22:46:32

This shows that the job failed twice. I checked the alert log file & it says:

Tue Jun 21 22:42:32 2005
Errors in file D:\orant\RDBMS80\trace\orclSNP0.TRC:
ORA-12012: error on auto execute of job 41
ORA-02394: exceeded session limit on IO usage, you are being logged off
ORA-06512: at line 1

While the user SCOTT is assigned DEFAULT profile. And LOGICAL_READS_PER_SESSION is set to UNLIMITED. Then I executed the following:
        
SQL> exec dbms_job.run(41)

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03/12/1981        950         95         30
      7902 FORD       ANALYST         7566 03/12/1981       3000        300         20
      7934 MILLER     CLERK           7782 23/01/1982       1300        130         10
         1 hash

Can you suggest what is going on?

thanks

 

Hi Tom -

You mentioned in one of your responses that you need to be logged in as the owner of a job to manipulate it, however I was wondering if you can create a job for a different user. I do not see an argument in dbms_job for the "owner" of a job, meaning that I can not specify from my DBA account that I want to create a job in SCOTT's account. I know I can specify a different schema owner to be used while parsing the job (using alter session set current_schema), but the owner of the job would still be the account I used to create the job. Please correct me if I am wrong.

Thanks.

Kashif

Hi Tom

would you please clarify the allocation of jobs to instances on RAC. I understand about job affinity. Suppose I have the affinity set to zero (0). Any instance may then pick up the job and run it, or be told to run it.

Is there a mechanism that prevents all instances in the RAC from running the job? Can you explain how it works? Where does it reside on the RAC, or on which instance, and how can I inspect what it's doing?

Many thanks. Keep up the good work!

Hi Tom,

We are taking export of a database on a periodic basis by using unix script in crontab.

Is it possible to use dbms_job for taking export by using unix script in Oracle 9iR2 ?

if yes , will you pl show me how to do that ?

regards & thanks
pjp

Hi Tom,

If I have a job submitted in two node RAC environment and that job is running(I said running) on one particular node. While the job running, for any reason that node fails, then what is the situation of that job that is running. Is there any chance that Oracle automatically failover that job and run through the other node or that job parmanently dies for ever until and unless we resubmitt that job through another node ( or just resubmitt whatever it is )

Thanks

Hi Tom

Tones of thanks !!

If suppose I didn't lock that job to run on any particular node then what is the scenario? I assume probably that will be started on another node at the time of next day or next time when that should start, is this correct?

My situation here is, we have some non stop kind of jobs, but need not run on any particualr node !! If suppose for some node balances we take one node down ( the on-site insane folks take shutdown some times node down with out interfering DBA ) so in that case the running job die and and can't start until the next submission time occurs. Or any suggestion to achieve this smartly like submitt job for every 1 minute or 2 minutes, is this kind of good solution that you recommend.

Tom,

I am bit confused here !!. So if the job running node dies and that job is scheduled for non-stop kind then that job moves started running from other node, is this your answer ? Of course that job is not locked to run on any particular instance. I couldn't figure exactly from the above answere what you gave to me

Thanks

Tom,

This is related to one of the follow-ups on this thread, where using dbms_job.RUN with ctx_ddl.sync_index results in an error. It doesn't look like this was ever resolved and I have encountered the same problem, in the process of trying to diagnose somebody else's problem. I can use dbms_job.SUBMIT and ctx_ddl.sync_index with no error and can use dbms_job.RUN on other jobs without ctx_ddl.sync_index, but the combination of dbms_job.RUN and ctx_ddl.sync_index results in an error. Obviously, using dbms_job.SUBMIT is sufficient, so this is just an academic question, out of curiosity and wondering if it is a bug or if I am missing something, like some explicitly granted permission on some obscure underlying whatever. I experimented with various things, but couldn't figure it out. The trace file says that the index does not exist, but obviously it does. I have provided a very simple demonstration of what works and what does not, followed by the contents of the trace file and the script that I used, so that you can attempt to reprorduce the error. Although the trace file says Windows 2000, it is actually Windows XP Home Edition.

Looking forward to your response,
Barbara

scott@ORA92> create table test_tab (col1 varchar2(60))
2 /

Table created.

scott@ORA92> insert into test_tab values ('first test record')
2 /

1 row created.

scott@ORA92> create index mydoc_index on test_tab (col1) indextype is ctxsys.context
2 /

Index created.

scott@ORA92> insert into test_tab values ('second test record')
2 /

1 row created.

scott@ORA92> commit
2 /

Commit complete.

scott@ORA92> select * from test_tab where contains (col1, 'test') > 0
2 /

COL1
------------------------------------------------------------
first test record

scott@ORA92> variable job number
scott@ORA92> begin
2 dbms_job.submit
3 (:job,
4 'ctx_ddl.sync_index (''MYDOC_INDEX'');',
5 sysdate,
6 'sysdate+1/96');
7 commit;
8 end;
9 /

PL/SQL procedure successfully completed.

scott@ORA92> execute dbms_lock.sleep (10)

PL/SQL procedure successfully completed.

scott@ORA92> select * from test_tab where contains (col1, 'test') > 0
2 /

COL1
------------------------------------------------------------
second test record
first test record

scott@ORA92> insert into test_tab values ('third test record')
2 /

1 row created.

scott@ORA92> commit
2 /

Commit complete.

scott@ORA92> select * from test_tab where contains (col1, 'test') > 0
2 /

COL1
------------------------------------------------------------
second test record
first test record

scott@ORA92> select to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual
2 /

TO_CHAR(SYSDATE,'DD-
--------------------
06-aug-2005 17:03:00

scott@ORA92> execute dbms_job.run (:job)
BEGIN dbms_job.run (:job); 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


scott@ORA92> print job

JOB
----------
792

scott@ORA92> select to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual
2 /

TO_CHAR(SYSDATE,'DD-
--------------------
06-aug-2005 17:03:01

scott@ORA92>


-- contents of trace file:
Dump file d:\oracle\admin\ora92\udump\ora92_ora_3452.trc
Sat Aug 06 16:11:57 2005
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
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
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: ora92

...

*** 2005-08-06 17:03:01.000
ORA-12012: error on auto execute of job 792
ORA-20000: Oracle Text error:
DRG-10502: index MYDOC_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CTX_DDL", line 1328
ORA-06512: at line 1


-- test script for reproduction of error:
create table test_tab (col1 varchar2(60))
/
insert into test_tab values ('first test record')
/
create index mydoc_index on test_tab (col1) indextype is ctxsys.context
/
insert into test_tab values ('second test record')
/
commit
/
select * from test_tab where contains (col1, 'test') > 0
/
variable job number
begin
dbms_job.submit
(:job,
'ctx_ddl.sync_index (''MYDOC_INDEX'');',
sysdate,
'sysdate+1/96');
commit;
end;
/
execute dbms_lock.sleep (10)
select * from test_tab where contains (col1, 'test') > 0
/
insert into test_tab values ('third test record')
/
commit
/
select * from test_tab where contains (col1, 'test') > 0
/
select to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual
/
execute dbms_job.run (:job)
print job
select to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual
/
exec dbms_job.remove (:job)
drop table test_tab
/

did the dbms_job.run with tracing on:

PARSING IN CURSOR #15 len=127 dep=2 uid=33 oct=3 lid=33 tim=1097088634312254 hv=2545474735 ad='616a493c'
SELECT /*+  ORDERED  */'X'   FROM CTXSYS.DR$INDEX,SYS.USER$ U  WHERE IDX_OWNER# = U.USER#  AND U.NAME = :b1  AND IDX_NAME = :b2
END OF STMT
PARSE #15:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1097088634312250
BINDS #15:
 bind 0: dty=1 mxl=32(06) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=64 offset=0
   bfp=b6f20460 bln=32 avl=06 flg=05<b>
   value="PUBLIC"</b>
 bind 1: dty=1 mxl=32(11) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=0 offset=32
   bfp=b6f20480 bln=32 avl=11 flg=01<b>
   value="MYDOC_INDEX"</b>
EXEC #15:c=0,e=214,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1097088634312598
FETCH #15:c=0,e=85,p=0,cr=9,cu=0,mis=0,r=0,dep=2,og=4,tim=1097088634312762
STAT #15 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=9 r=0 w=0 time=80 us)'
EXEC #13:c=20000,e=48877,p=0,cr=34,cu=0,mis=0,r=0,dep=1,og=4,tim=1097088634350865
ERROR #13:err=20000 tim=672726461
*** 2005-08-07 08:46:01.575
ORA-12012: error on auto execute of job 88
ORA-20000: Oracle Text error:
DRG-10502: index MYDOC_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CTX_DDL", line 1328
ORA-06512: at line 1
XCTEND rlbk=1, rd_only=1


so, it is a botched username.  The issue comes from a prior SQL statement (this is the dbms_job.run example):


PARSING IN CURSOR #14 len=104 dep=2 uid=33 oct=3 lid=33 tim=1097088634307916 hv=3939871214 ad='616a54f4'
SELECT TABLE_OWNER,TABLE_NAME,DB_LINK   FROM SYS.DBA_SYNONYMS  WHERE OWNER = :b1  AND SYNONYM_NAME = :b2
END OF STMT
PARSE #14:c=0,e=2458,p=0,cr=5,cu=0,mis=1,r=0,dep=2,og=0,tim=1097088634307906
BINDS #14:
 bind 0: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=64 offset=0
   bfp=b6f21c58 bln=32 avl=03 flg=05<b>
   value="SYS"</b>
 bind 1: dty=1 mxl=32(11) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=0 offset=32
   bfp=b6f21c78 bln=32 avl=11 flg=01
   value="MYDOC_INDEX"
EXEC #14:c=0,e=557,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1097088634308614
FETCH #14:c=0,e=89,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=4,tim=1097088634308774


and in job queue doing the same:

PARSING IN CURSOR #7 len=104 dep=2 uid=33 oct=3 lid=33 tim=1097089442005688 hv=3939871214 ad='616a54f4'
SELECT TABLE_OWNER,TABLE_NAME,DB_LINK   FROM SYS.DBA_SYNONYMS  WHERE OWNER = :b1  AND SYNONYM_NAME = :b2
END OF STMT
PARSE #7:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1097089442005680
BINDS #7:
 bind 0: dty=1 mxl=32(09) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=64 offset=0
   bfp=b6ff0c9c bln=32 avl=09 flg=05<b>
   value="OPS$TKYTE"</b>
 bind 1: dty=1 mxl=32(11) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=0 offset=32
   bfp=b6ff0cbc bln=32 avl=11 flg=01
   value="MYDOC_INDEX"
EXEC #7:c=0,e=244,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1097089442006073
FETCH #7:c=0,e=139,p=0,cr=5,cu=0,mis=0,r=0,dep=2,og=4,tim=1097089442006270



So, something wrong there, thanks for the simple test case -- with this little bit of research and the test case I'll file a bug. 

Thank you very much for figuring it out and filing the bug.

I'm trying to implement a procedure using DBMS_JOB to create threads, or forks as you explained above. I especially like how by using a table and then having the job read from it, you avoid constant hard parsing of the job.

Our DBA is telling us this is a very bad idea for several reasons and I wonder if you'd spend some time providing answers.

1. Failures in DBMS_JOBs write to an error log which causes a DBA to be paged. If the SMTP server is down and an email is sent via a job, it could wake an on call DBA up in the middle of the night.

2. Each new job increments the Job ID and we could run out of them.

3. If there's a problem and you kill the job, finding the session to kill is hard because it's a background/server session and so you can't identify it by machine. It looks like every other job at that point.



Argh. My response to 1 is that the parse and page process should be better at distinguishing what the problem is and whether or not someone should be paged.

For 2, the datatype for job ID is just NUMBER, so I'd have to create more than 9 followed by 37 9's before running out. Ah, but what happens then?

to 3, Not so sure since i'm not allowed access to the session table. This is where I really need an answers.

Thanks.

I've been suggesting, commenting, whining that we really should be exploiting DBMS_APPLICATION_INFO to make watching the active processes more informative as to what is going on.


then when you suggested using it to keep the sessions straight and obvious so our DBA doesn't have to think hard, it really brought it together.

Thanks.

Hi Tom,
Thanks in Advance.
Tom I had scheduled a backup procedure to execute daily at 23:00 through dbms_job. Sometime it is giving errors like
ORA-12012: error on auto execute of job 948996
ORA-12005: may not schedule automatic refresh for times in the past
Tue Aug 30 23:02:03 2005
I am not finding any clue about this why it is giving this error.

Hi Tom,
Thanks in Advance.
Tom I had scheduled a backup procedure to execute daily at 23:00 through dbms_job. Sometime it is giving errors like
ORA-12012: error on auto execute of job 948996
ORA-12005: may not schedule automatic refresh for times in the past
Tue Aug 30 23:02:03 2005
I am not finding any clue about this why it is giving this error.

Hi Tom,
Thanks in advance .When I am trying to schedule a job I am getting the error ORA-12005 .I am pasting the code below .

----------------Once at 23:00----------------
DECLARE
jobno NUMBER;
v2 varchar2(10) :='''N''';
BEGIN
DBMS_JOB.SUBMIT
(job => jobno
,what => 'begin proc_test1; end;'
,next_date => SYSDATE
,interval => 'trunc(sysdate)+23/24');
COMMIT;
END;
------------- Once at 00:00 ----------------
DECLARE
jobno NUMBER;
v2 varchar2(10) :='''N''';
BEGIN
DBMS_JOB.SUBMIT
(job => jobno
,what => 'begin proc_test2; end;'
,next_date => SYSDATE
,interval => 'trunc(sysdate+1)');
COMMIT;
END;
---------------------------------------------------------------------
I have not shutdown the database or did any activities to hinder the scheduling.But still the error is occuring .
Please give me a solution by which I can rectify this error.

Hi Tom,
Thanks for the very quick response.
'trunc(sysdate)+23/24' ,By giving this interval I am trying to execute a job every day at 23 PM .It does'nt come up with this error allways ,But frequently this error is occuring.I want to schedule a job for execution every day at 23 PM,Can you please give me a better solution for that,Than what I am using.I also have an other doubt ,Is this dbms_job utility that powerful ,Can we control the interval to a precition of micro seconds in this?If not which is the other best scheduling utility?

To make the interval more obvious (and to avoid a lot of 60,24 etc in the call), and to prevent the drifting of the start times, I usually use an interval like this...

DECLARE
jobno NUMBER;
v2 varchar2(10) :='''N''';
BEGIN
DBMS_JOB.SUBMIT
(job => jobno
,what => 'begin proc_test2; end;'
,next_date => SYSDATE
,interval => 'trunc(sysdate) + interval ''47'' HOUR)';
COMMIT;
END;


This will queue it up at 11:00:00PM the next day. It is even a better solution on jobs I have that run every 5 minutes...

interval => TRUNC(SYSDATE + INTERVAL '3' MINUTE,'MI')

The TRUNCATE keeps it queued up to start on exact minute boundary, even though it does start a few seconds later, at least it doesn't keep drifting.

Hi Tom,
What is the correct way to schedule a weekly job? I did the following and got an error. I want the job to run at 1:00 AM every Saturday. Thank you for your help.

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT (:jobno,'MY_PROCEDURE;','trunc(NEXT_DAY(sysdate,'SATURDAY'))+1/24','trunc(NEXT_DAY(sysdate+7,'SATURDAY'))+1/24');
COMMIT;
END;
/

ERROR at line 2:
ORA-06550: line 2, column 65:
PLS-00103: Encountered the symbol "SATURDAY" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like
between ||

Hi Tom,

We found out that sometimes, the procedure cannot see the record invoked from the insert trigger on the same table.

The sequence is like this:
1. Insert into a table A -- SeqNo being the PK
2. Trigger fires AFTER Insert on Table A.
3. Inside Trigger, We call a procedure to submit a job with :new.SeqNo as Parameter.
4. Job submits a procedure with same :new.SeqNo as Parameter.
5. But this procedure returns exception "No Data Found" **intermittently** for select from Table A where seq_request=<Param SeqNo>.

We are wondering how this can happen. Can you please throw some light on this.
Code:
CREATE OR REPLACE TRIGGER Trig_Appl_NextRequest AFTER INSERT ON Trn_Appl_Requests FOR EACH ROW
DECLARE
M_START VARCHAR2(10);
BEGIN
Pkg_Shell.Pr_Submit_Job(:New.Seq_Request);
EXCEPTION WHEN OTHERS THEN
Pkg_Shell.Error_Log_Update(:New.Seq_Request,'TERR', SUBSTR(SQLERRM,1,100));
END;
------------------------------------------------------------
PROCEDURE Pr_Submit_Job (p_Seq_Request NUMBER,p_st_request VARCHAR2 DEFAULT '0') AS
PRAGMA AUTONOMOUS_TRANSACTION;
m_Job NUMBER (10);
BEGIN
DBMS_JOB.SUBMIT(m_Job , 'Pkg_Shell.PR_WF_PROC ('||p_Seq_Request||');',SYSDATE);
COMMIT;
EXCEPTION WHEN OTHERS THEN
Pkg_Shell.Error_Log_Update(p_Seq_Request,'JERR', 'Job Error'||SUBSTR(SQLERRM,1,80));
END;
------------------------------------------------------------
PROCEDURE PR_WF_PROC(p_seq_request NUMBER) AS

m_appstatus Trn_Appl_Requests.St_Request%TYPE;
m_prevseqnr trn_appl_requests.seq_prev_request%TYPE;
m_cur_id_wf_step Trn_Appl_Requests.Id_Wf_Nextstep%TYPE :='0';
m_cur_id_wf_section Trn_Appl_Requests.Id_Wf_section%TYPE :=NULL;
m_SEMCallNo NUMBER(2) :=1;

exc_invalid_seqno EXCEPTION;

BEGIN
SELECT st_request,seq_prev_request, NVL(id_wf_nextstep,'0'), id_wf_section, NVL(No_SEMCall,0)
INTO m_appstatus,m_prevseqnr, m_cur_id_wf_step, m_cur_id_wf_section, m_SEMCallNo
FROM trn_appl_requests
WHERE seq_request = p_Seq_Request;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE exc_invalid_seqno;
END;
------------------------------------------------------------

Tom,

I need to run SQL script every day at 3 pm.
1. Can I import the result into Excel file or csv file.
2 can I write it to a new file depending on date.
'somename' ||'_9282005'

Tom,
Thanks for the suggestions and that is a real eye opener.

As you said, if we remove PRAGMA AUTONOMOUS TRANSACTION from the procedure, then we will not be able to give the *COMMIT*, since it is raised from the Trigger..it will throw the error "Cannot commit inside a trigger".

Now without commit, how can the job be submitted?

Hi,
I am trying schedule a database job using dbms_job as following (every 15 seconds):

conn AAA/BBB@instance
declare
l_job number;
begin
dbms_job.submit( l_job,
'PKG_REC_IDX_RDO.PRC_REC_IDX_RDO;',
sysdate,
'sysdate+1/5760' );
commit;
end;
/


job_queue_processes=4,
job_queue_interval =60.

Database version is 8.1.7.4 on Win2000 SP4

The job appears in user_jobs (LOG_USER ,PRIV_USER,SCHEMA_USER = AAA, n°Job =20,BROKEN=N) but never gets executed.

if i try to execute the job myself, it works ...

conn AAA/BBB@instance
exec dbms_job.run(20);

==> OK, next_date and next_sec are refreshed ...


I don't understand why job does not run whereas I positioned job_queue_processes, job_queue_interval and commit into my job.

No trace in my alert.log

I also add a commit word in package body called in job, and time execution package is less than 1 second.


How can I get it to work?
Thanks in advance.

Hi ,

here, the solution to force job schedule when job_queue_processes, job_queue_interval, and commit on job are ok :

conn sys/XXX
exec dbms_ijob.set_enabled(true);


best regards

Hi Tom,

I have a simple job in 9i.
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'insert into x values (1);');
COMMIT;
END;
/

1. Since it doesn't have a schedule it just runs once.
But does it leave a log of how it was executed?
Successfully or not?

2. If I have job with schedule dates. Will it create a log
entry whenever it had run in the past? Is there a view
to see the log.

Appreciate your help.

thanks
Mateen

Thanks for the response. Yes 10g do have.

Thank you.

Hi,
I know that once the job is successfully completed, it doesn't leave any trace in dba_jobs.

Is there any place in oracle where I can find at what time the job is completed? Any log... Any alert....

I am developing some programs based on dbms_job for 9i and
would like to get the exact time stamp when the job finished.

I will highly appreciate your help.

Just to add to my previous question
Is the log info stored somewhere in aq$ objects? for
completed jobs in 9i.. Please advise.
thanks

I am new to DBMS_JOBS. Have read the Oracle Packages documentation and this entire thread and been on metalink and yes I still need help - aargh

There are several existing jobs in the 9i database. I need to send email when an error occurs. For the existing jobs there is a DBMS_JOBS column called failures but it appears to be zero in all existing jobs. (That's good-right)

Can I assume that I could design this request to notify by :
1) Create new DBMS_JOB
That executes every 5 seconds
new JOB_FAIL_NOTIFY procedure
JOB_FAIL_NOTIFY procedure
Reads DBA_JOBS selects all columns from DBA_JOBS if failures GT
Generates an email message and populates all the varaibles from DBA_JOBS (since I understand the DBA_JOBS_RUNNING is just that.

Here is the sendmail proc - swiped from this thread it looks like and is currently implemented here.

CREATE OR REPLACE PROCEDURE "SEND_MAIL" (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
l_mailhost VARCHAR2 (255) := 'XX.XXX.X.X';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, p_sender);
UTL_SMTP.rcpt (l_mail_conn, p_recipient);
UTL_SMTP.open_data (l_mail_conn);
UTL_SMTP.write_data (l_mail_conn, p_message);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
END;
/



OR - I have also derived from reading this thread - that every jobs that exists today needs to be modified to include exceptions into a table that records an error - meaning that the column failures remains zero..

Happy Holiday and have fun abroad.

PS I have not bought your book yet Oracle One on One that is mentioned in this thread several times but it is NOW on my Christmas list 8>)

So here is an existing production job: No exception handling done - have no idea if it fails - really want to know. So if I understand

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'dbms_refresh.refresh(''"CCPROVIDER2"."PROVIDER_CONTRACT"'');'
,next_date => to_date('23/12/2005 11:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate + 1'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;
exception
when others then
exec sendmail(p1,p2,p3)send_email(....);
RAISE; /* <<<=== very very important */
end;


---

so I would need to remove the existing jobs and replace them with the above and like magic email will be sent on failure?

That is a better design - I got lost in all the information. I always prefer a simple method versus complicating something.

Always kind and most helpful. Thanks so much again for the assist. Around here all I have to say is "Tom Kyte said..." (like old Merrill Lynch(?) commercial) and everyone stops and listens...8>) Hey don't let it go to your head...

Hi,

How could i get a completed definition,with all parameters of a one jobs from a dba_job.

Means if i want to delete one job and recreate same job.
How could i do that.

Thanks,
Atul

Hi Tom,
Does this interval
sysdate+10/1440 denote "for every 10 minutes" ??

Please do reply.
Bye!

Hi Tom,

I have a job where the interval runs the following statement:
(select itvdate from v_sched);

That statment will return the next time the job has to run again.

The code for the view is the following:

create or replace view v_sched as
select case
when (select rj from t_sched where jid=(select sys_context( 'userenv', 'fg_job_id') from dual))='Y'
then (sysdate + (15/24/60))
else (select ndate from t_sched where jid=(select sys_context( 'userenv', 'fg_job_id') from dual))
end itvdate
from dual
/

If the job is run manually, there are no problems, but every time the job is run by the background process, after it is done, the job removes itself from the queue, and there is nothing connecting with it on dba_jobs. I have checked the alert log and there are no errors at all.

But If I replace the sys_context part in the view by the job number that I want to run, then the job runs without any problems through the background process using the exact same interval "(select itvdate from v_sched);". Do you have any ideas for why the sys_context will cause the job to be removed from the queue if the background process executes it directly?
This is happening on an Oracle 9.2.0.5 on a RAC enviroment.

Thanks for any help on this.

Hi, Tom

If I want to schedule a job to run every 10 minutes, assume that first time the job
started at 00:00 and if the job itself takes about 15 minutes to complete. Would the second job be scheduled at the expected 00:10? Or what can we do to ensure this?

Thanks.

Thanks for your reply, Tom.

So if I want to do the aggregate caculation every 10 minutes and sometimes the caculation will exceed 10 minutes, I need to implment this by application. One idea is saving the last caculated end time, then the job check how many intervals should be caculated. If it exceed time for one time, it can caculate it in the following interval.

Are there other good ideas for such case?

Maybe give the example is better. Sorry for the unclear description.

We want to caculate some performance indicators every 10 minutes( such as average response time for the application deals with the request during each 10 minutes, the time can be caculated from the logs that is stored in the database). For example, at 00:12, we will caculate the average response time between 00:00 and 00:10. Then at 00:22, we want to caculate the value between 00:10 and 00:20.
Sometimes maybe the load is very high, the caculation can not be finished in the interval. For example, the caculation that begins at 00:12 don't finish at 00:22. Then at 00:32, we need to caculate the repsonse time between 00:10 and 00:20, between 00:20 and 00:30.

Thanks for your help.

I have to call 5 procedures from a PL/SQL block.
All these procedures have to be called at the same time.
It means that the call to the second procedure should not wait for first procedure to finish. similarly the third one should not wait for the second one to finish. Each procedure should be running at the back end.

I used DBMS_JOB package.

eclare
njob number(10):=1;
njob1 number(10):=2;
begin
dbms_job.submit(njob, 'proc1;');
dbms_job.submit(njob1, 't11;');
dbms_job.run(njob);
dbms_job.run(njob1);
end;

Is this correct? I have to pass parameters also to the procedure.

I have 2 databases on same server. On both of them I created similar job. On one it run when it is scheduled to, on another it run only when I execute EXECUTE DBMS_JOB.RUN(22);
For both databases parameter job_queue_processes is 10. Both databases are 9.2.0.6.0 VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'CHECK_PGMPC_DATA.send_email_check_data;' ,
SYSDATE, 'SYSDATE + 1/24');
commit;
END;
/

Can you please let me know what am I doing wrong on this database?
Thanks

> $ps -ef | grep cjq
bbowman 6694 6603 0 09:25:10 pts/6 0:00 grep cjq
ncdsora 18028 1 0 03:03:43 ? 0:04 ora_cjq0_CDS

You are right cjq process is not running for the second database.
How can I start it?
I thought that it would start when job_queue_processes parameter is different than 0.

What did you want to day with sentence “and you have 2 times the maximum number of databases on that machine!”?
Thanks
Branka

>ALTER SYSTEM SET JOB_QUEUE_PROCESSES =0;
System altered.
>ALTER SYSTEM SET JOB_QUEUE_PROCESSES =10;


> $ps -ef | grep cjq
bbowman 20041 6603 0 10:47:23 pts/6 0:00 grep cjq
ncdsora 20033 1 0 10:47:08 ? 0:00 ora_cjq0_PUB
ncdsora 18028 1 1 03:03:43 ? 0:05 ora_cjq0_CDS

But job is still not running.
What else can be problem?
Thanks
Branka

Job was not broken, it was scheduled for future.
job queue processes (cjq) stopped working.
Nothing is in alert log.
It didn't send email while it was working, and next day it didn't work.
Where should I search for the solution?
Thanks

It was schedule one hour in advance.
I deleted job several times, and it didn't execute when I create again.
Our database get bounce every night (scheduled process that I can not change), and my job queue processes (cjq) stopped working again.
I don't know if it is related.
So I have 2 problems now.
Why job queue processes stop working, and don't send any message about it.
Why DBMS_JOB doesn't work when I do have job queue processes working.
Thanks

I run select below. Error just doesn't have any sense.
Parameter job_queue_processes did not changed.

select name, error from V$BGPROCESS
where name like 'CJQ%';
NAME ERROR
------------------------------ ---------
CJQ0 448

I did open tar, but waited 2 days, and than I deleted user QS, QS_CBADM, QS_CS, QS_os and QS_WS, and problem is solved.

Hi Tom,

How are the jobs balanced in RAC?
How do Oracle decide which instance to run a job not the other, are they balanced across instances

Thanks Tom,

One more question. Which view could I see which job running on which instance?
dba_jobs_running shows instance 0

Hi Tom,

We have jobs which run on the database using dbms_job, the start_date and next_date parameters are specified in UTC.

The OS unix date is in PDT Timezone and will change to PST wherein the difference of 1 hr will come.

The job runs at 9:00 UTC which is 2:00 AM PDT. Now with the PST Timezone coming in we want the job to execute and get re-scheduled itself depending on the chage of TIMEZONE.

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

Select * from user_jobs;
414 USER_A USER_A USER_A 17/08/2006 08:59:18 08:59:18 18/08/2006 08:59:18 08:59:18 35239 N sysdate + 1 0
proc_jb;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YYYY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000

What is the way out to get it implemented that the job execution switches by an hour.

Hi Tom,

This job runs under TZ=UTC, so when 9:00 AM UTC which is 2:00 AM PDT it gets executed.

There is a dependent job which runs at 3:30 AM PDT which has been set via crontab.

Not lets suppose the PDT timezone shifts to PST, so all machines will change automatically and will be 1 hr ahead. Now the dbms_job will run as it is since it is not dependent on PDT/PST Timezone but on UTC. However the cronjob will be running at 3:30 AM PST which will actually be 1 hr earlier instead.

The dbms_job might still be running and we don't want the IInd job to execute till we have enough bandwidth of 1 hr 30 mins.

I hope that explains the situation.Is there any SQL function which converts the machine specific TZ to UTC and then execute the procedure.

The database entry for TZ is picked from /etc/oratab

mat:/opt/app/oracle/product/10.1.0.4/:Y:UTC, where mat is the instance name,/opt/app/oracle/product/10.1.0.4/ is the ORACLE_HOME, Y signifies AUTO START and UTC is the TZ for the database and when the database was created we specified UTC as the TIMEZONE.

USR@MAT>select dbtimezone from dual;

DBT
---
UTC

Hope it clarifies.

Hi Tom,

I am using RAC, but from dba_jobs_running view the instance always diplays 0.

Thanks in advance

Hi Tom,

I know that the database job is going to run as UTC, but what I need is that when the OS TZ switches from PDT to PST then the job should execution time should also shift accordingly.

If it is running at 9:00 AM UTC then it should run at 10:00 AM UTC since PDT PST time difference is 1 hr.

That's what my idea was. Is there any SQL function which can get this result. I am sure it might be there but just could n't get to that function.

Thanks

Hi tom,

I think that the NEW_TIME sql date function can be useful, but still look for your inputs.

Select to_char(new_time(TO_DATE('18-AUG-2006 08:59:18','DD-MON-YYYY hh24:MI:SS'),'GMT','PDT'),'DD-MON-YYYY hh24:mi:ss') from dual
dwuser@ADMW>/

TO_CHAR(NEW_TIME(TO_
--------------------
18-AUG-2006 01:59:18

Select to_char(new_time(TO_DATE('18-AUG-2006 08:59:18','DD-MON-YYYY hh24:MI:SS'),'GMT','PST'),'DD-MON-YYYY hh24:mi:ss') from dual
2 /

TO_CHAR(NEW_TIME(TO_
--------------------
18-AUG-2006 00:59:18

so there is a gap of 1 hr between PDT and PST TZ's. what we want is that irrespective of OS timezone the job should execute at 1:00 AM.

Can be decode function work here within the dbms_job.

If the OS timezone is PDT then
job should execute at sysdate and
else if OS timezone is PST then
job should execute at sysdate + 1/24

for the Ist iteration and for NEXT_DATE it should be

If the OS timezone is PDT then
job should execute at sysdate+1 and
else if OS timezone is PST then
job should execute at sysdate+1 + 1/24

Please help!

tried truncate a table which has the lob segments.

select count(*) from tab
and returned 0.

but
truncate tab;

is hanging?

why?

Tom

I tried to search Oracle documentation at tahiti.oracle.com, for information on any relationship /limitation between number of dbms_job jobs that can be run versus number of CPU's on the database server, but I could not get any information. Does such a relation/limitation exist?

I want to pose the same question with respect to number of queues and DB server CPU's?

thanks

Tom:

Is there a way to copy existing jobs from one 8i database to another 9i database.
The exp/imp does not seem to copy them.

yes, it, does.....

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

Commit complete.

ops$tkyte%ORA9IR2> !exp userid=/ 'owner=ops$tkyte'

Export: Release 9.2.0.8.0 - Production on Wed Oct 25 16:09:16 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ops$tkyte%ORA9IR2> exec dbms_job.remove( :n );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> select what from user_jobs;

no rows selected

ops$tkyte%ORA9IR2> !imp userid=/ full=y

Import: Release 9.2.0.8.0 - Production on Wed Oct 25 16:09:19 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


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

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
Import terminated successfully without warnings.

ops$tkyte%ORA9IR2> select what from user_jobs;

WHAT
------------------------------
null;

ops$tkyte%ORA9IR2>
 

Tom:

I schedule a few jobs using a similar script in your book. I check the user jobs and they are there scheduled and should run every morning at 1:00 AM. The next day I come to check if
the jobs are there, and I do not find them. Even the ones schedukled did not run. What could possibly be going on?


SQL>variable l_job number
SQL>exec dbms_job.submit(:l_job,'REPORT1_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
SQL>exec dbms_job.submit(:l_job,'REPORT3_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
SQL>exec dbms_job.submit(:l_job,'INV_QTR_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
       
       
       
       
SQL> select job,to_char(last_date,'DD-MON-YY HH24:MI') Last_date,
  2             to_char(next_date,'DD-MON-YY HH24:MI') Next_date,
  3             broken,substr(interval,1,20) interval,substr(what,1,30) what,failures
  4      from user_jobs where trunc(next_date)=trunc(sysdate+1)
  5  order by 3 desc
  6  /

       JOB LAST_DATE       NEXT_DATE       B INTERVAL
---------- --------------- --------------- - --------------------
WHAT                             FAILURES
------------------------------ ----------
        43                 28-OCT-06 01:00 N trunc(sysdate)+1+1/2
REPORT1_LOAD(job);

        44                 28-OCT-06 01:00 N trunc(sysdate)+1+1/2
REPORT3_LOAD(job);

        45                 28-OCT-06 01:00 N trunc(sysdate)+1+1/2
INV_QTR_LOAD(job);


3 rows selected.


The next day they disappear

SQL> @jobs

no rows selected 

Looks like you are missing commit after submitting jobs.
You are able to see in the jobs table as you are in the same session.

TOm:

The commit was missing. I added that. However would not his parameter cause a problem when set to 0

job_queue_processes = 0

Tom:

I did not understand your answer. Was that parameter causing the jobs to never run every night because it was set to 0?

Is there a way to determine how long a scheduled job ran? I am looking at data dictionary view all_jobs -- it does not give me this info. We have scheduled jobs to run at 11:00 pm everynight. we wanted to determine how long it took for a job to complete. Thanks.

TOm:

I am still having issues with the jobs being run. They are not being reun nightly. The current parameter job_queue_processes is set to 2. Can that be the problem if you have over 5 jobs scheduled to run same time.


Thanks

Hi Tom-

Using a 2 node 10gR2 RAC, each node with 4 CPU cores.

I see you've talked a bit about DBMS_JOB in a RAC environment, so would like to know if you've got any new information on it's behaviour.


The quick question:

Is there any particular "least-loaded-node" load balancing logic with DBMS_JOBS that are submitted in a cluster when not specifying node affinity? If so, how do we ensure that it's enabled?

I've looked everywhere and can't find anything, and am hoping you can shed some light on this for me.


The background:

I have a data loading application that I use to preload table data before our stress/load testing/tuning. It is a pl/sql package that will take the complete scope of the data generation and split it up into X number of DBMS_JOBs.

For instance, if we want to pre-load 200k users' data (about 500,000,000 rows of data), we split it up so that each job loads 50k users worth.

The application is definitely CPU bound, so I want to split the jobs up across the various nodes to spread the load across all available CPU cores.

To help distribute the load across the cluster, each job is submitted 2 minutes later than the previous one. This is to give the node a chance to fire up and register it's current load with the cluster. (It did NOT distribute the jobs effectively when I did not space out the start times). I do NOT specify any kind of instance affinity when I submit the job.

In our cluster development environment (which I set up and administer here in Canada), this works like a charm, and regardless of the number of nodes that are running it will fire up the next job on the least loaded node.

In our off-site BETA/PROD cluster environments (which I did not set up, nor administer, that's done by the Ops DBA team on-site at our Redwood Shores offices), it does NOT work. All of the jobs will spin up on a single node, while the other one node will not run any of the jobs. (Also, we're not live yet, but will be by the end of next month).

I do NOT know why, and I'd like to be able to ask the Ops DBA's to "do this" to enable it, if possible.

It seems that in the "problematic" BETA/PROD environments, whichever node was initially connected to by ANT (using a load-balanced JDBC URL) will be the one that runs ALL of the jobs.

This procedure takes about 6 hours to run, when properly distributed, so I want to try and have each core run one of the jobs (the SAN is HUGE so we have no IO issues). I'd like to ensure that the jobs are distributed effectively/evenly, and should one node go down or fail, have the other one pick up the job and run it. It's not doing that right now.


Possible Solution: I know that I could write some code that figures out how many active nodes there are before the jobs are submitted and then submit the jobs using node affinity, but then I'm screwed if that node goes down for some reason, as we REALLY need to ensure that the data is loaded come the start of the next business day for our global testing efforts.

THAT would mean writing some "check to see if it failed and re-submit it" code, which is just adding way more complexity than I'm hoping to have to deal with.


PLEASE tell me that you know of some magic "job_load_balancing_across_nodes=true" silver bullet!


As always, any thoughts/insight are more than welcome.


Thanks for your time...


...jeff

Hi Tom,
I have an urgent requirement to schedule and trigger a process when a certain condition is met.
I intend to perform this using dbms_job.
The requirement in detail is to start polling for the contents of a table from the first day of every month.
If there have been entries for that month(count(*) will do just fine),I want to schedule a procedure to immediately run.
Else, continue to schedule it till count(*) > 0.

Once the count(*) > 0 and the scheduling and processing completes, I would have to stop the polling and start the polling starting from the first day of next month.

The procedure actually sends out a application generated status email.
I am having some confusion in interval parameter specifically.
We are on 9.2.0.8.

Many Thanks.

Hi Tom

I'm starting jobs with

dbms_job.submit(v_num,'proc_x()');

Usually this works fine but if the procedure fails the job is automatically reastarted over and over until it completes succesfully.

How do I tell it to start only one time even if it fails?

Thanks in advance.

begin
  proc_x;
exception
  when others
  then
      LOG_THAT_ERROR_SOMEWHERE;
      EMAIL_YOUR_SELF;
end;

I am trying to use dbms_job to do some updates concurrently.

This is my settings. 

SQL> show parameter job;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_interval                   integer     60
job_queue_processes                  integer     3


Following is my test case. In case you want to use.

drop table t2
/
create table t2 (a integer, b integer, c integer)
/
declare
 i integer;
 lRowCount number(10);
begin
 for i in 1..20
 loop
  select count(1) into lRowCount from t2;
  insert into t2 select lrowCount + rownum, lrowCount + rownum, lRowCount +rownum from all_objects;
 end loop;
end;
/
analyze table t2 compute statistics
/

Now i run the below block. This is an update happening one by one.

SQL> begin
  2
  3  update t2 set a = a;
  4  commit;
  5  update t2 set b = b;
  6  commit;
  7  update t2 set c = c;
  8  commit;
  9
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:39.05
SQL>

So i wanted to speed up the process. So after some surfing in asktom and oracle doc i found i could run the three updates concurrently by submiting in job using dbms_job.


SQL>   declare
  2    x integer;
  3    begin
  4
  5    SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t2 set a = c;');
  6    SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t2 set b = c;');
  7    SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t2 set c = c;');
  8
  9    commit;
 10    end;
 11    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>

Cool the block ran in no time!! am i a genius  or what??

Oops!! Oops!!

Then did the below query again.. and again.. and again..


SQL>  select job, this_date, this_sec, next_date, next_sec, broken, total_time from user_jobs
  2  /

       JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC B TOTAL_TIME
---------- --------- -------- --------- -------- - ----------
         7 19-SEP-07 15:33:46 19-SEP-07 15:33:18 N          6
         8                    19-SEP-07 15:33:18 N          0
         9                    19-SEP-07 15:33:18 N          0

after some time..

SQL> /

       JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC B TOTAL_TIME
---------- --------- -------- --------- -------- - ----------
         8 19-SEP-07 15:34:11 19-SEP-07 15:33:18 N          5
         9 19-SEP-07 15:34:15 19-SEP-07 15:33:18 N          1

after some MORE time..

SQL>  /

       JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC B TOTAL_TIME
---------- --------- -------- --------- -------- - ----------
         8 19-SEP-07 15:34:11 19-SEP-07 15:33:18 N         79
         9 19-SEP-07 15:34:15 19-SEP-07 15:33:18 N         75

after some time..

SQL>  /

       JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC B TOTAL_TIME
---------- --------- -------- --------- -------- - ----------
         9 19-SEP-07 15:34:15 19-SEP-07 15:33:18 N         91


after some MORE MORE time..

SQL>  /

       JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC B TOTAL_TIME
---------- --------- -------- --------- -------- - ----------
         9 19-SEP-07 15:34:15 19-SEP-07 15:33:18 N        112


finally!!!

SQL>  /

no rows selected

so for the third job to complete it took like almost the same time as the block that i executed first.

So my question is, Dint these 3 jobs run concurrently???

I thought i told oracle to run them concurrently :-) (Did i miss some thing?)

One more thing where can i find the details of the completed jobs. Because once the job is finished its moved out of user_jobs.

Note: The updates are fast in my test case. But in our production it takes long time as it has a large volume of data so we are seriously looking for some stuff like this.

Waiting for you to let me know how i should tell oracle :-)

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

Sorry tom. I was suppose to try some thing like this.

SQL> declare
  2  x integer;
  3  begin
  4
  5  SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t1 set a = a;');
  6  SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t2 set a = a;');
  7  SYS.DBMS_JOB.SUBMIT(job => x, what => 'update t3 set a = a;');
  8
  9  commit;
 10  end;
 11  /

Once all the job got executed it disappears from user_jobs. So if i want to see the details of the finished jobs. Like when it started when it ended and how long it took to run where can i see.

No tom the second one went parallel only. Sorry that if dint mention that.

I think i should have my owne log to find when they started and ended.

Thank's for your time.