#

| Chapter 5 Oracle Advanced Queuing |

## 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

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.