CODEWITHSUNDEEP
oracle-databaseplsqljob-scheduling
jaychapani
jaychapani

Reputation: 1509

ORACLE:DBMS Jobs database connection

Currently in my application I have one dbms job which triggers on interval of 3 min. Dbms job makes call to procedure which uses global temporary tables.

Scenario:

1.Let's say my first job is triggered at time (X) and it will take around an hour to complete.
2.Meanwhile second job is triggered at time (X+3 min).

Question:

Does these two dbms jobs shares same connection? I mean will they use same session for global temporary table?

Upvotes: 0

Views: 400

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

Oracle won't automatically start the same job twice, even if it takes longer to complete than the interval specified by INTERVAL. If you force it to run twice, they will not share the database session.

EDIT: To make it to run in parallel at a particular interval, you could do it like that:

 declare id number;
 begin
   dbms_job.submit(id, 'myjobstarter;', sysdate, 'sysdate+3/24/60');
   commit;
 end;

 create procedure myjobstarter as
   id number;
 begin
   dbms_job.submit(id, 'longRunningProcedure;');
   commit;
 end;

So your regular job submits a new job every time it runs. But of course you are likely to run into some locking problems or other race conditions this way. Also be aware that DBMS_JOB won't run infinitely jobs in parallel, so the real behaviour might become a bit unpredictable.

Upvotes: 1

Related Questions