DBA Tips Archive for Oracle


Introducing Oracle Advanced Queuing - (Oracle 8i)

by Jeff Hunter, Sr. Database Administrator


  1. Overview
  2. Terminology


In client/server environments, requests are usually executed immediately. This is often referred to as online or connected execution of work. In this model, a program sends a request to another program and waits until the reply arrives. The sender and receiver are tightly coupled. This model is suitable for applications that need a reply before they can proceed. The major drawback is that applications must be available and running to work. In the event of network or other kind of failure, the entire application can potentially come to a halt.

However, in some situations deferred or disconnected execution may be preferred, or even required. This is referred to as disconnected or deferred communication between programs. In this model, programs in the role of producers place requests in a queue and then proceed with their work. Programs in the role of consumers retrieve requests from the queue and act on them. To protect against failure, there is a need for requests to be stored persistently and processed exactly once. This can be achieved by combining persistent queuing with transaction protection. Oracle8 provides a queuing technology that does not depend on the use of TP-monitors or any other evolving Message-Oriented Middleware infrastructure.

Queuing implements deferred execution of work. When a request for work is entered, queuing defers its processing until the requester has completed the task, process or transaction that created the request.


Oracle AQ (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 functionality:


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 Oracle 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 DBMS_AQADM.CREATE_QUEUE_TABLE procedure.


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 Oracle 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 only be set to values 0 or 1. If this parameter is set to 1, one time manager process will be created as a background process to monitor the messages.


#init<sid>.ora parameter file

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 17-Aug-2006 12:43:34 EDT
Page Count: 14221