[Chapter 5] 5.6 Oracle AQ Database Objects

5.6 Oracle AQ Database Objects

Oracle AQ relies on a variety of database objects to get its job done. Some objects are created for each queue table established. Other objects are created at the time that Oracle AQ is installed.

5.6.1 Objects Per Queue Table

When a queue table is created, Oracle AQ defines a database table to hold all the messages for all queues in that queue table, as well as a view that allows a user to both query from and change (with caution and the guidance of Oracle Support) messages stored in queues of the queue table.

5.6.1.1 The database table for queue data

The name of the database table has the following form,

<queue_table>

where queue_table is the name of the queue table created. Table 5.3 shows the columns of this view.

Table 5.3: Columns in the Database Table for Queue Data
Name	Description	Datatype
Q_NAME	Name of the queue (remember that you can have more than one queue in a queue table)	VARCHAR2(30)
MSGID	Unique identifier of the message	RAW(16)
CORRID	Optional correlation identifier value provided by the user	VARCHAR2(30)
PRIORITY	Message priority	NUMBER
STATE	Message state	NUMBER
DELAY	The point in time to which the message is delayed for dequeuing	DATE
EXPIRATION	Number of seconds in which the message will expire after its message state is set to READY	NUMBER
TIME_MANAGER_INFO	For internal use only	DATE
LOCAL_ORDER_NO	For internal use only	NUMBER
CHAIN_NO	For internal use only	NUMBER
CSCN	For internal use only	NUMBER
DSCN	For internal use only	NUMBER
ENQ_TIME	Date-time at which the message was enqueued	DATE
ENQ_UID	User ID of the session that enqueued the message	NUMBER
ENQ_TID	ID number of the transaction that enqueued this message	VARCHAR2(30)
DEQ_TIME	Date-time at which the message was dequeued	DATE
DEQ_UID	User ID of the session that dequeued the message	NUMBER
DEQ_TID	ID number of the transaction that dequeued this message	VARCHAR2(30)
RETRY_COUNT	Number of retries at dequeuing the message	NUMBER
EXCEPTION_QSCHEMA	Name of the schema containing the exception queue for this message	VARCHAR2(30)
EXCEPTION_QUEUE	Name of the exception queue for this message	VARCHAR2(30)
STEP_NO	For internal use only	NUMBER
RECIPIENT_KEY	For internal use only	NUMBER
DEQUEUE_MSGID	Message ID for the dequeue operation	RAW(16)
USER_DATA	Payload of the queue (<user_data>); this might be a RAW value or the contents of the object that was placed in the queue	RAW or <object_type>

You will find it useful to execute queries directly against this base table when you need to examine dequeue status information for messages that reside in a multiple consumer queue.

Here, for example, is the kind of query you might write to view the list of the agents that consumed a message with the following ID,

452F77CD652E11D1B999B14141A17646.

SELECT consumer, transaction_id, deq_time, deq_user
  FROM THE 
     (SELECT CAST (history AS SYS.AQ$_DEQUEUE_HISTORY_T)
        FROM msg_qtable
       WHERE msgid = 452F77CD652E11D1B999B14141A17646).

where SYS.AQ$_DEQUEUE_HISTORY_T is a nested table of type SYS.AQ$_DEQUEUE_HISTORY. This dequeue history object type is defined in catqueue.sql as follows:

CREATE TYPE sys.aq$_dequeue_history_t 
AS OBJECT
( consumer  VARCHAR2(30),	-- identifies dequeuer
  transaction_id VARCHAR2(22),	-- M_LTID, transaction id of dequeuer
  deq_time  DATE,               -- time of dequeue
  deq_user  NUMBER,             -- user id of client performing dequeue
  remote_apps  VARCHAR2(4000),	-- string repn. of remote agents
  agent_naming NUMBER,           -- how the message was sent to agent
  propagated_msgid RAW(16));

5.6.1.2 The queue table view

The name of the view for a queue table has the following form,

AQ$<queue_table>

where queue_table is the name of the queue table created. Table 5.4 shows the columns of this view. Notes about this view and its usage are included after the table.

Table 5.4: Columns in the Queue Table View
Name	Description	Type
QUEUE	Name of the queue (remember you can have more than one queue in a queue table)	VARCHAR2(30)
MSG_ID	Unique identifier of the message	RAW(16)
CORR_ID	Optional correlation identifier value provided by the user	VARCHAR2(128)
MSG_PRIORITY	Message priority	NUMBER
MSG_STATE	Message state	VARCHAR2(9)
DELAY	Point in time to which the message is delayed for dequeuing	DATE
EXPIRATION	Number of seconds in which the message will expire after its message state is set to READY	NUMBER
ENQ_TIME	Date-time at which the message was enqueued	DATE
ENQ_USER_ID	User ID of the enqueuing process	NUMBER
ENQ_TXN_ID	Transaction ID of the enqueue action	VARCHAR2(30)
DEQ_TIME	Date-time at which the message was dequeued	DATE
DEQ_USER_ID	User ID of the dequeuing process	NUMBER
DEQ_TXN_ID	Transaction ID of the dequeue action	VARCHAR2(30)
RETRY_COUNT	Number of attempts to dequeue the message	NUMBER
EXCEPTION_QUEUE_OWNER	Owner of exception queue	VARCHAR2(30)
EXCEPTION_QUEUE	Name of exception queue for this message	VARCHAR2(30)
USER_DATA	Payload of the queue (<user_data>); this might be a RAW value or the contents of the object which was placed in the queue	RAW or <object_type>

Note the following about using the queue table view:

The AQ administrator can use the SQL language to examine the contents of any queue or queue table.
The dequeue columns are relevant only for single consumer queues. If you want to examine the dequeue history of messages in a multiple consumer queue, you will need to examine the underlying database table owned by SYS that contains the message data (see the next section).

5.6.2 Data Dictionary Objects

This section documents the database objects in the data dictionary that contain information for all queue tables and queues to which you have access.

5.6.2.1 The DBA_QUEUE_TABLES view

You can obtain information about all the queue tables created in your instance by examining the DBA_QUEUE_TABLES data dictionary view. The USER_QUEUE_TABLES view will show you all information about queue tables defined in your schema. Its columns are identical to the DBA version, except that there is no OWNER column. Table 5.5 lists the columns of the DBA_QUEUE_TABLES view.

Table 5.5: Columns in DBA_QUEUE_TABLES View
Name	Description	Type
OWNER	The schema owning the queue table	VARCHAR2(30)
QUEUE_TABLE	Name of the queue table	VARCHAR2(30)
TYPE	Type of payload in the queue table (either `RAW' or `OBJECT')	VARCHAR2(7)
OBJECT_TYPE	Name of the object type if the type of the queue table is OBJECT	VARCHAR2(61)
SORT_ORDER	A sort order for queues in the queue table, if specified	VARCHAR2(22)
RECIPIENTS	A value indicating whether it is a single consumer queue table (DBMS_AQADM.SINGLE) or a multiple consumer queue table (DBMS_AQADM.MULTIPLE)	VARCHAR2(8)
MESSAGE_GROUPING	The type of message grouping, either DBMS_AQADM.NONE or DBMS_AQADM.TRANSACTION	VARCHAR2(13)
USER_COMMENT	Comment provided by the user to associate with the queue table	VARCHAR2(50)

5.6.2.2 The DBA_QUEUES view

You can obtain information about all the queues created in your instance by examining the DBA_QUEUES data dictionary view. The USER_QUEUES view will show you all information about queues defined in your schema. Its columns are identical to the DBA version except that there is no OWNER column. Table 5.6 lists the columns of the DBA_QUEUES view.

Table 5.6: Columns in DBA_QUEUES View
Name	Description	Type
OWNER	The schema owning the queue	VARCHAR2(30)
NAME	Name of the queue	VARCHAR2(30)
QUEUE_TABLE	Name of the queue table that contains this queue	VARCHAR2(30)
QID	Unique identifier for queue	NUMBER
QUEUE_TYPE	Type of the queue, either DBMS_AQADM.NORMAL_QUEUE or DBMS_AQADM.EXCEPTION_QUEUE	VARCHAR2(5)
MAX_RETRIES	Maximum number of dequeue attempts that are allowed on messages in this queue	NUMBER
RETRY_DELAY	Number of seconds before a dequeue retry can be attempted	NUMBER
ENQUEUE_ENABLED	Flag indicating whether or not (YES or NO) the enqueue operation is enabled for this queue	VARCHAR2(7)
DEQUEUE_ENABLED	Flag indicating whether or not (YES or NO) the dequeue operation is enabled for this queue	VARCHAR2(7)
RETENTION	Number of seconds a message is retained in the queue after dequeuing	NUMBER
USER_COMMENT	Comment provided by the user to associate with the queue table	VARCHAR2(50)

5.6.2.3 The DBA_JOBS view

For Oracle 8.0.4 and later, AQ provides a view to the schedules currently defined for propagating messages. Table 5.7 shows the columns of the DBA_JOBS view.

Table 5.7: Columns in the DBA_JOBS View
Name	Description	Type
SCHEMA	Schema owning the queue	VARCHAR2(30)
QNAME	Name of the source queue	VARCHAR2(30)
DESTINATION	Name of the destination; currently limited to being a database link (dblink) name	VARCHAR2(128)
START_DATE	Date at which propagation will be started	DATE
START_TIME	Time of day at which propagation will be started; this is stored in a string of format HH:MM:SS	VARCHAR2(8)
WINDOW	Duration of the propagation window in seconds	NUMBER
NEXT_TIME	String containing a date expression that evaluates to the starting date/time of the next propagation window	VARCHAR2(128)
LATENCY	Maximum number of seconds AQ will wait before it attempts to propagate messages during a propagation window	NUMBER

Check this view to see if a particular combination of source queue and destination have been scheduled for propagation. If so, you can determine the job ID or job number for the propagation by examining the SYS.AQ$_SCHEDULES table. Apply this job number to the DBA_JOBS view to find out:

The last time that propagation was scheduled.
The next time that propagation will occur.
The status of the job. If the job is marked as broken, you can check for errors in the trace files generated by the job queue processes in the $ORACLE_HOME/log directory.

5.6.2.4 The GV$AQ and V$AQ dynamic statistics views

Oracle AQ provides two views for retrieving dynamic statistics for AQ operations: GV$AQ and V$AQ. The columns for these views are exactly the same, but they contain different data:

GV$AQ view: Provides information about the numbers of messages in various states for the entire database. It consolidates information from all instances when it is queried in an Oracle parallel server environment.
V$AQ view: Contains information about the messages in a specific database instance. It does this by examining AQ statistics stored in the System Global Area (SGA) of the instance.

Table 5.8 lists the columns of the GV$AQ and V$AQ views.

Table 5.8: Columns in GV$AQ and V$AQ Views
Name	Description	Type
QID	Unique identifier of a queue; its value matches the same column in DBA_QUEUES and USER_QUEUES	NUMBER
WAITING	Number of messages in the WAITING state	NUMBER
READY	Number of messages in the READY state	NUMBER
EXPIRED	Number of messages in the EXPIRED state	NUMBER
TOTAL_WAIT	Number of seconds for which messages in the queue have been waiting in the READY state	NUMBER
AVERAGE_WAIT		NUMBER


5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)		5.7 Oracle AQ Examples