9 Oracle Streams AQ & Messaging Gateway Views

9.1 DBA_QUEUE_TABLES: All Queue Tables in Database

The DBA_QUEUE_TABLES view contains information about the owner instance for a queue table. A queue table can contain multiple queues. In this case, each queue in a queue table has the same owner instance as the queue table. Its columns are the same as those in ALL_QUEUE_TABLES.

9.2 USER_QUEUE_TABLES: Queue Tables in User Schema

The USER_QUEUE_TABLES view is the same as DBA_QUEUE_TABLES with the exception that it only shows queue tables in the user's schema. It does not contain a column for OWNER.

9.3 ALL_QUEUE_TABLES: Queue Tables Queue Accessible to the Current User

The ALL_QUEUE_TABLES view describes queue tables accessible to the current user.

9.4 DBA_QUEUES: All Queues in Database

The DBA_QUEUES view specifies operational characteristics for every queue in a database. Its columns are the same as those ALL_QUEUES.

9.5 USER_QUEUES: Queues In User Schema

The USER_QUEUES view is the same as DBA_QUEUES with the exception that it only shows queues in the user's schema.

9.6 ALL_QUEUES: Queues for Which User Has Any Privilege

The ALL_QUEUES view describes all queues on which the current user has enqueue or dequeue privileges. If the user has any Advanced Queuing system privileges, like MANAGE ANY QUEUE, ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE, this view describes all queues in the database.

9.7 DBA_QUEUE_SCHEDULES: All Propagation Schedules

The DBA_QUEUE_SCHEDULES view describes all the current schedules in the database for propagating messages.

9.8 USER_QUEUE_SCHEDULES: Propagation Schedules in User Schema

The USER_QUEUE_SCHEDULES view is the same as DBA_QUEUE_SCHEDULES with the exception that it only shows queue schedules in the user's schema.

9.9 QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege

The QUEUE_PRIVILEGES view describes queues for which the user is the grantor, grantee, or owner. It also shows queues for which an enabled role on the queue is granted to PUBLIC.

9.10 AQ$Queue_Table_Name: Messages in Queue Table

The AQ$Queue_Table_Name view describes the queue table in which message data is stored. This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.

In a queue table that is created with the compatible parameter set to '8.1' or higher, messages that were not dequeued by the consumer are shown as "UNDELIVERABLE". You can dequeue these messages by msgid. If the Oracle Streams AQ queue process monitor is running, then the messages are eventually moved to an exception queue. You can dequeue these messages from the exception queue with an ordinary dequeue.

A multiconsumer queue table created without the compatible parameter, or with the compatible parameter set to '8.0', does not display the state of a message on a consumer basis, but only displays the global state of the message.

When a message is dequeued using the REMOVE mode, DEQ_TIME, DEQ_USER_ID, and DEQ_TXN_ID are updated for the consumer that dequeued the message.

You can use MSGID and ORIGINAL_MSGID to chain propagated messages. When a message with message identifier m1 is propagated to a remote queue, m1 is stored in the ORIGINAL_MSGID column of the remote queue.

Beginning with Oracle Database 10g, AQ$Queue_Table_Name includes buffered messages. For buffered messages, the value of MSG_STATE is one of the following:

• IN MEMORY

  Buffered messages enqueued by a user

• DEFERRED

  Buffered messages enqueued by a capture process

• SPILLED

  User-enqueued buffered messages that have been spilled to disk

• DEFERRED SPILLED

  Capture-enqueued buffered messages that have been spilled to disk

• BUFFERED EXPIRED

  Expired buffered messages

9.11 AQ$Queue_Table_Name_S: Queue Subscribers

The AQ$Queue_Table_Name_S view provides information about subscribers for all the queues in any given queue table. It shows subscribers created by users with DBMS_AQADM.ADD_SUBSCRIBER and subscribers created for the apply process to apply user-created events. It also displays the transformation for the subscriber, if it was created with one. It is generated when the queue table is created.

This view provides functionality that is equivalent to the DBMS_AQADM.QUEUE_SUBSCRIBERS() procedure. For these queues, Oracle recommends that the view be used instead of this procedure to view queue subscribers. This view is created only for 8.1-compatible queue tables.

9.12 AQ$Queue_Table_Name_R: Queue Subscribers and Their Rules

The AQ$Queue_Table_Name_R view displays only the subscribers based on rules for all queues in a given queue table, including the text of the rule defined by each subscriber. It also displays the transformation for the subscriber, if one was specified. It is generated when the queue table is created.

This view is created only for 8.1-compatible queue tables.

9.13 DBA_QUEUE_SUBSCRIBERS: All Queue Subscribers in Database

The DBA_QUEUE_SUBSCRIBERS view returns a list of all subscribers on all queues in the database. Its columns are the same as those in ALL_QUEUE_SUBSCRIBERS.

9.14 USER_QUEUE_SUBSCRIBERS: Queue Subscribers in User Schema

The USER_QUEUE_SUBSCRIBERS view returns a list of subscribers on queues in the schema of the current user. Its columns are the same as those in ALL_QUEUE_SUBSCRIBERS except that it does not contain the OWNER column.

9.15 ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges

The ALL_QUEUE_SUBSCRIBERS view returns a list of subscribers to queues that the current user has privileges to dequeue from.

9.16 DBA_TRANSFORMATIONS: All Transformations

The DBA_TRANSFORMATIONS view displays all the transformations in the database. These transformations can be specified with Advanced Queue operations like enqueue, dequeue and subscribe to automatically integrate transformations in messaging. This view is accessible only to users having DBA privileges.

9.17 DBA_ATTRIBUTE_TRANSFORMATIONS: All Transformation Functions

The DBA_ATTRIBUTE_TRANSFORMATIONS view displays the transformation functions for all the transformations in the database.

9.18 USER_TRANSFORMATIONS: User Transformations

The USER_TRANSFORMATIONS view displays all the transformations owned by the user. To view the transformation definition, query USER_ATTRIBUTE_TRANSFORMATIONS.

9.19 USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions

The USER_ATTRIBUTE_TRANSFORMATIONS view displays the transformation functions for all the transformations of the user.

9.20 DBA_SUBSCR_REGISTRATIONS: All Subscription Registrations

The DBA_SUBSCR_REGISTRATIONS view lists all the subscription registrations in the database.

9.21 USER_SUBSCR_REGISTRATIONS: User Subscription Registrations

The USER_SUBSCR_REGISTRATIONS view lists the subscription registrations in the database for the current user. Its columns are the same as those in DBA_SUBSCR_REGISTRATIONS.

9.22 AQ$INTERNET_USERS: Oracle Streams AQ Agents Registered for Internet Access

The AQ$INTERNET_USERS view provides information about the agents registered for Internet access to Oracle Streams AQ. It also provides the list of database users that each Internet agent maps to.

9.23 (G)V$AQ: Number of Messages in Different States in Database

The (G)V$AQ view provides information about the number of messages in different states for the whole database.

In a Real Application Clusters environment, each instance keeps its own Oracle Streams AQ statistics information in its own System Global Area (SGA), and does not have knowledge of the statistics gathered by other instances. When a GV$AQ view is queried by an instance, all other instances funnel their Oracle Streams AQ statistics information to the instance issuing the query.

9.24 (G)V$BUFFERED_QUEUES: All Buffered Queues in the Instance.

The V$BUFFERED_QUEUES view displays information about all buffered queues in the instance. There is one row per queue.

9.25 (G)V$BUFFERED_SUBSCRIBERS: Subscribers for All Buffered Queues in the Instance

The V$BUFFERED_SUBSCRIBERS view displays information about the subscribers for all buffered queues in the instance. There is one row per subscriber per queue.

9.26 (G)V$BUFFERED_PUBLISHERS: All Buffered Publishers in the Instance

The V$BUFFERED_PUBLISHERS view displays information about all buffered publishers in the instance. There is one row per queue per sender. The values are reset to zero when the database (or instance in an Oracle RAC environment) restarts.

9.27 (G)V$PERSISTENT_QUEUES: All Active Persistent Queues in the Instance

The V$PERSISTENT_QUEUES view displays information about all active persistent queues in the database since the queues' first activity time. There is one row per queue. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.

9.28 (G)V$PERSISTENT_SUBSCRIBERS: All Active Subscribers of the Persistent Queues in the Instance

The V$PERSISTENT_SUBSCRIBERS view displays information about all active subscribers of the persistent queues in the database. There is one row per instance per queue per subscriber. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.

9.29 (G)V$PERSISTENT_PUBLISHERS: All Active Publishers of the Persistent Queues in the Instance

The V$PERSISTENT_PUBLISHERS view displays information about all active publishers of the persistent queues in the database. There is one row per instance per queue per publisher. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.

9.30 (G)V$PROPAGATION_SENDER: Buffer Queue Propagation Schedules on the Sending (Source) Side

The V$PROPAGATION_SENDER view displays information about buffer queue propagation schedules on the sending (source) side. The values are reset to zero when the database (or instance in a Real Application Clusters (RAC) environment) restarts, when propagation migrates to another instance, or when an unscheduled propagation is attempted.

9.31 (G)V$PROPAGATION_RECEIVER: Buffer Queue Propagation Schedules on the Receiving (Destination) Side

The V$PROPAGATION_RECEIVER view displays information about buffer queue propagation schedules on the receiving (destination) side. The values are reset to zero when the database (or instance in a Real Application Clusters (RAC) environment) restarts, when propagation migrates to another instance, or when an unscheduled propagation is attempted.

9.32 (G)V$SUBSCR_REGISTRATION_STATS: Diagnosability of Notifications

The V$SUBSCR_REGISTRATION_STATS view provides information for diagnosability of notifications.

9.33 V$METRICGROUP: Information about the Metric Group

This V$METRICGROUP view displays information about the metric group for each of the four major Streams components: capture, propagation, apply, and queue.

9.34 (G)V$STREAMSMETRIC: Streams Metrics for the Most Recent Interval

This view displays the capture, propagation, and apply metrics for the most recent interval.

9.35 (G)V$STREAMSMETRIC_HISTORY: Streams Metrics Over Past Hour

This view returns all metric values for streams messages over the past hour. It has the same form as (G)V$STREAMSMETRIC: Streams Metrics for the Most Recent Interval.

9.36 (G)V$QUEUEMETRIC: Queue Metrics for the Most Recent Interval

This view displays the queue metrics for the most recent interval.

9.37 (G)V$QUEUEMETRIC_HISTORY: Queue Metrics Over Past Hour

This view returns all queue metric values over the past hour. It has the same shape as (G)V$QUEUEMETRIC: Queue Metrics for the Most Recent Interval.

9.38 DBA_HIST_STREAMSMETRIC: Streams Metric History

This view displays view provides catalog access to streams metric history.

9.39 DBA_HIST_QUEUEMETRIC: Queue Metric History

This view displays view provides catalog access to queue metric history.

9.40 MGW_GATEWAY: Configuration and Status Information

This view lists configuration and status information for Messaging Gateway.

9.41 MGW_AGENT_OPTIONS: Supplemental Options and Properties

This view lists supplemental options and properties for a Messaging Gateway agent.

9.42 MGW_LINKS: Names and Types of Messaging System Links

This view lists the names and types of messaging system links currently defined.

9.43 MGW_MQSERIES_LINKS: WebSphere MQ Messaging System Links

This view lists information for the WebSphere MQ messaging system links. The view includes most of the messaging system properties specified when the link is created.

9.44 MGW_TIBRV_LINKS: TIB/Rendezvous Messaging System Links

This view lists information for TIB/Rendezvous messaging system links. The view includes most of the messaging system properties specified when the link was created.

9.45 MGW_FOREIGN_QUEUES: Foreign Queues

This view lists information for foreign queues. The view includes most of the queue properties specified when the queue is registered.

9.46 MGW_JOBS: Messaging Gateway Propagation Jobs

This view lists information for Messaging Gateway propagation jobs. The view includes most of the job properties specified when the propagation job was created, as well as other status and statistical information.

9.47 MGW_SUBSCRIBERS: Information for Subscribers

This view lists configuration and status information for Messaging Gateway subscribers. The view includes most of the subscriber properties specified when the subscriber is added, as well as other status and statistical information.

9.48 MGW_SCHEDULES: Information about Schedules

This view lists configuration and status information for Messaging Gateway schedules. The view includes most of the schedule properties specified when the schedule is created, as well as other status information.