Oracle Advanced Queuing

Last updated on November 23rd, 2015 at 01:28 pm

Oracle Advanced Queuing provides message queuing as an integrated part of the Oracle Server, thereby creating a message-enabled database.

Oracle uses the following components to deliver AQ fuctionality:

A message is the smallest unit of information inserted into and retrieved from a queue. It contains user data (payload) in the form of an object type,
and control information (metadata) that can be used to specify message ordering, a time window for execution, message priority and so on. A message
can reside in only one queue. Messages are created using the DBMS_AQ.ENQUEUE PL/SQL procedure. Messages can later be read by the same or another
application using the DBMS_AQ.DEQUEUE PL/SQL procedure. The application developer is responsible for the application’s use of the ENQUEUE and DEQUEUE
procedure calls.

A queue is a repository of messages. There are two types of queues: user queues and exception queues. A user queue is for normal message processing.
Messages that cannot be retrieved or processed from user queues are transferred to an exception queue. Queues are created, altered, started,
stopped and dropped using the Oracle8 AQ administrative interface package DBMS_AQADM. There is no limit to the number of queues defined in the database.

Queue Tables
Queues are stored in queue tables. Each queue table is a database table and contains one or more queues. Each queue table contains a default exception
queue. Each column of a queue table represents message queues and rows represent individual messages. You create queue tables using the

An agent is a queue user. There are 2 types of agents: producers and consumers. A producer places messages in queues; this process is called
enqueuing. A consumer retrieves messages; this process is called dequeuing. Any number of producers and consumers may access the queue at a given time.
An agent is identified by its name, address and protocol. The address and protocol are reserved for future use. Agents insert messages into and
retrieve messages from queues using the Oracle8 AQ operational interface procedures DBMS_AQ.ENQUEUE and DBMS_AQ.DEQUEUE.

Time Manager
The time manager is a background process that monitors the messages in the queue. It provides the mechanism for message expiration, retry and delay.
A parameter called AQ_TM_PROCESSES should be specified in the init.ora parameter file if you want to perform time monitoring on queue messages.
This parameter can be set to values 0 or 10. If this parameter is set to >1, time manager processes will be created as a background processes to
monitor the messages.

DBMS_AQADM, DBMS_AQ Packages and AQ Object Types:

Oracle AQ operations are performed using the DBMS_AQADM and DBMS_AQ packages. Initially, only user ‘SYS’ has access to AQ operations and execute privileges
for the procedures in the DBMS_AQADM and DBMS_AQ packages. Access to AQ objects and operations is granted to users through the AQ_ADMINISTRATOR_ROLE
and AQ_USER_ROLE roles. The AQ_ADMINISTRATOR_ROLE role grants execute privilege to procedures in both the DBMS_AQADM and DBMS_AQ packages. The
AQ_USER_ROLE role grants execute privileges to procedures in the DBMS_AQ package.

To access AQ object types, the procedure DBMS_AQADM.GRANT_TYPE_ACCESS must be executed for the AQ administrator by the user ‘SYS’. The AQ administrator
can subsequently execute the DBMS_AQADM.GRANT_TYPE_ACCESS procedure to grant access for AQ object types to other AQ users. This procedure must be executed
for any AQ user that wishes to perform any administrative operation involving a multiple consumer queue.

Leave a Reply