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.
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).
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));
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:
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.
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.
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:
Table 5.8 lists the columns of the GV$AQ and V$AQ views.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.