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.
22.214.171.124 The database table for queue data
The name of the database table has the following form,
where queue_table is the name of the queue table created. Table 5.3 shows the columns of this view.
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));
126.96.36.199 The queue table view
The name of the view for a queue table has the following form,
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.
Note the following about using the queue table view:
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.
188.8.131.52 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.
184.108.40.206 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.
220.127.116.11 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.
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:
18.104.22.168 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:
Table 5.8 lists the columns of the GV$AQ and V$AQ views.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.