Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I moved this question from stackoverflow to here as it was marked on hold as off-topic. On Oracle 10g2 DB I have set up multiple event triggered DMBS_SCHEDULER jobs and all of them are subscribed for the same event queue. The problem is when I call the function that enqueues the message in the advanced queue, therefore triggering the scheduler event all of the subscribed jobs are executed only once.

  • In the loop I am calling procedure that enqueues the messages in the queue. (I did check the message count in the queue and it matches the loop count);

  • There are multiple jobs (or a single job - I did test both cases the result is the same) that have event schedule and are subscribed to the particular queue;

  • After executing this loop, I do check the message count in the queue and the instance count of the new jobs in sys.dba_scheduler_job_log. Result - all the messages in the queue have been processed (meaning it is empty) and only one instance per each job have been registered in scheduler_job_log table;

  • The thing is that if I add DBMS_LOCK.sleep(1); after at the end of the each iteration in the loop then all of the job instances are registered in the scheduler_log_table fine.

Why do all of the other queue messages processed from the queue without running the job? Are there any DBMS_SCHEDULER settings to be adjusted?

share|improve this question
    
I'd try the forums at Oracle Technet -- much more experience of this sort of thing there. –  David Aldridge Jul 8 '13 at 23:49

2 Answers 2

up vote 0 down vote accepted

This is a documented behavior so "it's not a bug, it's a feature".

http://docs.oracle.com/cd/B19306_01/server.102/b14231/scheduse.htm#CHDIAJEB says:

events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.

share|improve this answer
1  
Thank you for pointing to this note. I did not somehow notice it when reading docs. As these event triggered jobs are not reliable in 10g and there are no company plans to migrate to 11g, then I had to think of some workaround. I changed these jobs to execute with a time period schedule (every minute), and they are calling and extra procedure that is checking and dequeueing these event messages and then processing business logic. –  skujins Jul 22 '13 at 16:06
    
Hmm "not reliable". Isn't it possible to have a plain and simple stored procedure (not a job) that is notified on each enqueue? I never tested this, so I cannot vouch, but I would say it is a traditional way and would be reliable (that is, it wouldn't silently dequeue events). –  kubanczyk Jul 24 '13 at 20:44

And this documented behaviour can be bypassed by setting this attribute to the job before its enabled:

DBMS_SCHEDULER.set_attribute ('my_job_name', 'parallel_instances', TRUE);

share|improve this answer
    
Unfortunately this attribute is only available from Oracle 11GR2. –  skujins Feb 24 at 21:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.