Can you ensure there is only one client connected to Oracle AQ at any one time? Can you ensure there is only one client connected to Oracle AQ at any one time? oracle oracle

Can you ensure there is only one client connected to Oracle AQ at any one time?


There are multi-consumer queues, and single-consumer queues. Single consumer queues do not have subscribers. Any client can consume if has the access to.However, addressing your issue, you have few options, here are they in the order of ease of implementation:

Option 1: Access Control:

Oracle supports access control to dequeue at the queue level . So you can give this access to a single user, if the users are different

-> Application_A uses USER_A and only USER_A is given access to consume from QUEUE_A

Option 2: Use multi-consumer queues:

If you configured your queue as multi-consumer, and have consumer subscribing (multi-subscribers for whatever applications you use the queue for). The message will not be consumed from the queue until all subscribers have dequeued it. Hence, there can be no loss of messages for a given consumer. There are variation of this publish-subscribe model you can validate which is more suitable, but in general, a message would still be in the queue untill all subscribers consumes it or it timesout (which you can also control)

-> DR and Application_A both subscribe to multi-consumer queue QUEUE_MULTI. Each consumer (DR, Application_A) can consume (each only once) the message - as the message is not expired. The message is dequeued once all consumer have consumed it

Option 3: Use queue propagation:

Define a multi-consumer queue to propagate to another queue for which only one can dequeue from. Hence, a message received is propagated to other queues (can be remote for use of DR or other use of other applications). The message is propagated to the queue and marked as consumed by that queue once it reached. The message is not completely consumed until all consumers (incuding other queues) consumes it

-> DR and Application_A both has their own Queue for consuming these message. (QUEUE_DR, QUEUE_A). Define a multi-consumer queue QUEUE_MULTI. QUEUE_MULTI propagates to QUEUE_DR and QUEUE_A, etc. Once the message is propagated to all queues, the message is consumed from QUEUE_MULTI regardless of what happened in the respective queues QUEUE_A, QUEUE_DR


You could ensure that the two applications use the same user and that the user is only used for AQ (i.e. not for other tasks). That way you can limit the user to a single session. If one application is logged in the other will not be able to connect:

alter system set resource_limit=true scope=both;create profile only_one_session_profile limit sessions_per_user 1;alter user (your user) profile only_one_session_profile;

I haven't tested the code, but it should work. Not sure how your applications react when they are only allowed a single connection, though!