RelationalDBDesign RelationalDBDesign 


Advanced Queuing   «Prev 

Advanced Queuing in Oracle

Environment Setup

Administration and access privileges for advanced queuing are controled using two roles:
  1. AQ_ADMINISTRATOR_ROLE - Allows creation and administration of queuing infrastructure.
  2. AQ_USER_ROLE - Allows access to queues for enqueue and dequeue operations.
In the following examples I have used two schemas, one owning the queuing infrastructure and another with access to it.

CONNECT / AS SYSDBA
CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users;
GRANT connect TO aq_admin;
GRANT create type TO aq_admin;
GRANT aq_administrator_role TO aq_admin;
ALTER USER aq_admin QUOTA UNLIMITED ON users;

CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;
GRANT connect TO aq_user;
GRANT aq_user_role TO aq_user;

Queues are set up in an Oracle database
Queues are set up in an Oracle database

A producer creates a message and sends it to a particular queue
A producer creates a message and sends it to a particular queue

The queue holds the message until
The queue holds the message until ...

A consumer retrieves the message
A consumer retrieves the message, at which point it leaves the queue