Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 5.5 DBMS_AQADM: Performing AQ  Administrative Tasks (Oracle8 only)Chapter 5
Oracle Advanced Queuing
Next: 5.7 Oracle AQ Examples
 

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


Previous: 5.5 DBMS_AQADM: Performing AQ  Administrative Tasks (Oracle8 only)Oracle Built-in PackagesNext: 5.7 Oracle AQ Examples
5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)Book Index5.7 Oracle AQ Examples

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference