Indicates which instance is the primary owner of the queue table, or no primary owner if 0

SECONDARY_INSTANCE

NUMBER

-

Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.

OWNER_INSTANCE

NUMBER

-

Indicates which instance currently owns the queue table

USER_COMMENT

VARCHAR2(50)

-

User comment for the queue table

SECURE

VARCHAR2(3)

-

Indicates whether this queue table is secure (YES) or not (NO). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

Indicates which instance is the primary owner of the queue table, or no primary owner if 0

SECONDARY_INSTANCE

NUMBER

-

Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.

OWNER_INSTANCE

NUMBER

-

Indicates which instance currently owns the queue table

USER_COMMENT

VARCHAR2(50)

-

User comment for the queue table

SECURE

VARCHAR2(3)

-

Indicates whether this queue table is secure (YES) or not (NO). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

All Queues in Database View

Name of View

DBA_QUEUES

Purpose

Specifies operational characteristics for individual queues. The DBA_QUEUES view displays these characteristics for every queue in a database.

A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. If a dequeue transaction fails because the server process dies (including ALTERSYSTEMKILLSESSION) or SHUTDOWNABORT on the instance, then RETRY_COUNT is not incremented.

Maximum wait time to propagate a message during the propagation window

SCHEDULE_DISABLED

VARCHAR(1)

-

N if enabled; Y if disabled (schedule will not be executed)

PROCESS_NAME

VARCHAR2(8)

-

Name of Jnnn background process executing this schedule; NULL if not currently executing

SESSION_ID

VARCHAR2(82)

-

Session ID (SID, SERIAL#) of the job executing this schedule; NULL if not currently executing

INSTANCE

NUMBER

-

Real Application Clusters instance number executing this schedule

LAST_RUN_DATE

DATE

-

Date of the last successful execution

LAST_RUN_TIME

VARCHAR2(8)

-

Time of the last successful execution in HH:MI:SS format

CURRENT_START_DATE

DATE

-

Date the current window of this schedule was started

CURRENT_START_TIME

VARCHAR2(8)

-

Time the current window of this schedule was started in HH:MI:SS format

NEXT_RUN_DATE

DATE

-

Date the next window of this schedule will be started

NEXT_RUN_TIME

VARCHAR2(8)

-

Time the next window of this schedule will be started in HH:MI:SS format

TOTAL_TIME

NUMBER

-

Total time in seconds spent in propagating messages from the schedule

TOTAL_NUMBER

NUMBER

-

Total number of messages propagated in this schedule

TOTAL_BYTES

NUMBER

-

Total number of bytes propagated in this schedule

MAX_NUMBER

NUMBER

-

Maximum number of messages propagated in a propagation window

MAX_BYTES

NUMBER

-

Maximum number of bytes propagated in a propagation window

AVG_NUMBER

NUMBER

-

Average number of messages propagated in a propagation window

AVG_SIZE

NUMBER

-

Average size of propagated messages in bytes

AVG_TIME

NUMBER

-

Average time to propagate a message in seconds

FAILURES

NUMBER

-

Number of times execution failed. If it reaches 16, then the schedule is disabled.

LAST_ERROR_DATE

DATE

-

Date of the last unsuccessful execution

LAST_ERROR_TIME

VARCHAR2(8)

-

Time of the last unsuccessful execution in HH:MI:SS format

LAST_ERROR_MSG

VARCHAR2(4000)

-

Error number and error message text of the last unsuccessful execution

Queues for Which User Has Any Privilege View

Name of View

ALL_QUEUES

Purpose

Describes all queues accessible to the user.

Table 9-5 ALL_QUEUES View

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the queue

NAME

VARCHAR2(30)

NOT NULL

Name of the queue

QUEUE_TABLE

VARCHAR2(30)

NOT NULL

Queue table name

QID

NUMBER

NOT NULL

Unique queue identifier

QUEUE_TYPE

VARCHAR2(15)

-

Queue type

MAX_RETRIES

NUMBER

-

Number of dequeue attempts allowed

RETRY_DELAY

NUMBER

-

Number of seconds before retry can be attempted

ENQUEUE_ENABLED

VARCHAR2(7)

-

YES or NO

DEQUEUE_ENABLED

VARCHAR2(7)

-

YES or NO

RETENTION

VARCHAR2(40)

-

Number of seconds message is retained after dequeue

USER_COMMENT

VARCHAR2(50)

-

User comment for the queue

Note:

A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. If a dequeue transaction fails because the server process dies (including ALTERSYSTEMKILLSESSION) or SHUTDOWNABORT on the instance, then RETRY_COUNT is not incremented.

Queues for Which User Has Queue Privilege View

Name of View

QUEUE_PRIVILEGES

Purpose

Describes queues for which the user is the grantor, or grantee, or owner, or an enabled role or the queue is granted to PUBLIC.

Messages in Queue Table View

Name of View

AQ$Queue_Table_Name

Purpose

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.

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:

SPILLED

IN MEMORY

DEFERRED

DEFERRED SPILLED

Table 9-7 AQ$Queue_Table_Name View

Column

Datatype

NULL

Description

QUEUE

VARCHAR2(30)

-

Queue name

MSG_ID

RAW(16)

NOT NULL

Unique identifier of the message

CORR_ID

VARCHAR2(128)

-

User-provided correlation identifier

MSG_PRIORITY

NUMBER

-

Message priority

MSG_STATE

VARCHAR2(16)

-

Message state

DELAY

DATE

-

Number of seconds the message is delayed

DELAY_TIMESTAMP

TIMESTAMP

-

Number of seconds the message is delayed

EXPIRATION

NUMBER

-

Number of seconds in which the message expires after being READY

ENQ_TIME

DATE

-

Enqueue time

ENQ_TIMESTAMP

TIMESTAMP

-

Enqueue time

ENQ_USER_ID (8.0.4 or 8.1.3 queue tables)

NUMBER

-

Enqueue user ID

ENQ_USER_ID (10.1 queue tables)

VARCHAR2(30)

-

Enqueue user ID

ENQ_TXN_ID

VARCHAR2(30)

-

Enqueue transaction ID

DEQ_TIME

DATE

-

Dequeue time

DEQ_TIMESTAMP

TIMESTAMP

-

Dequeue time

DEQ_USER_ID (8.0.4 or 8.1.3 queue tables)

NUMBER

-

Dequeue user ID

DEQ_USER_ID (10.1 queue tables)

VARCHAR2(30)

-

Dequeue user ID

DEQ_TXN_ID

VARCHAR2(30)

-

Dequeue transaction ID

RETRY_COUNT

NUMBER

-

Number of retries

EXCEPTION_QUEUE_OWNER

VARCHAR2(30)

-

Exception queue schema

EXCEPTION_QUEUE

VARCHAR2(30)

-

Exception queue name

USER_DATA

-

-

User data

SENDER_NAME

VARCHAR2(30)

-

Name of the agent enqueuing the message (valid only for 8.1-compatible queue tables)

SENDER_ADDRESS

VARCHAR2(1024)

-

Queue name and database name of the source (last propagating) queue (valid only for 8.1-compatible queue tables). The database name is not specified if the source queue is in the local database.

SENDER_PROTOCOL

NUMBER

-

Protocol for sender address (reserved for future use and valid only for 8.1-compatible queue tables)

ORIGINAL_MSGID

RAW(16)

-

Message ID of the message in the source queue (valid only for 8.1-compatible queue tables)

CONSUMER_NAME

VARCHAR2(30)

-

Name of the agent receiving the message (valid only for 8.1-compatible multiconsumer queue tables)

ADDRESS

VARCHAR2(1024)

-

Queue name and database link name of the agent receiving the message.The database link name is not specified if the address is in the local database. The address is NULL if the receiving agent is local to the queue (valid only for 8.1-compatible multiconsumer queue tables)

PROTOCOL

NUMBER

-

Protocol for address of receiving agent (valid only for 8.1-compatible queue tables)

PROPAGATED_MSGID

RAW(16)

-

Message ID of the message in the queue of the receiving agent (valid only for 8.1-compatible queue tables)

ORIGINAL_QUEUE_NAME

VARCHAR2(30)

-

Name of the queue the message came from

ORIGINAL_QUEUE_OWNER

VARCHAR2(30)

-

Owner of the queue the message came from

EXPIRATION_REASON

VARCHAR2(19)

-

Reason the message came into exception queue. Possible values are TIME_EXPIRATION (message expired after the specified expired time), MAX_RETRY_EXCEEDED (maximum retry count exceeded), and PROPAGATION_FAILURE (message became undeliverable during propagation).

Note:

A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. If a dequeue transaction fails because the server process dies (including ALTERSYSTEMKILLSESSION) or SHUTDOWNABORT on the instance, then RETRY_COUNT is not incremented.

Queue Tables in User Schema View

Name of View

USER_QUEUE_TABLES

Syntax

This 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.

Table 9-8 USER_QUEUE_TABLES View

Column

Datatype

NULL

Description

QUEUE_TABLE

VARCHAR2(30)

-

Queue table name

TYPE

VARCHAR2(7)

-

Payload type

OBJECT_TYPE

VARCHAR2(61)

-

Name of object type, if any

SORT_ORDER

VARCHAR2(22)

-

User-specified sort order

RECIPIENTS

VARCHAR2(8)

-

SINGLE or MULTIPLE

MESSAGE_GROUPING

VARCHAR2(13)

-

NONE or TRANSACTIONAL

COMPATIBLE

VARCHAR2(5)

-

Indicates the lowest version with which the queue table is compatible

PRIMARY_INSTANCE

NUMBER

-

Indicates which instance is the primary owner of the queue table, or no primary owner if 0

SECONDARY_INSTANCE

NUMBER

-

Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.

OWNER_INSTANCE

NUMBER

-

Indicates which instance currently owns the queue table

USER_COMMENT

VARCHAR2(50)

-

User comment for the queue table

SECURE

VARCHAR2(3)

-

Indicates whether this queue table is secure (YES) or not (NO). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

Queues In User Schema View

Name of View

USER_QUEUES

Purpose

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

Table 9-9 USER_QUEUES View

Column

Datatype

NULL

Description

NAME

VARCHAR2(30)

NOT NULL

Queue name

QUEUE_TABLE

VARCHAR2(30)

NOT NULL

Queue table where this queue resides

QID

NUMBER

NOT NULL

Unique queue identifier

QUEUE_TYPE

VARCHAR2(20)

-

Queue type

MAX_RETRIES

NUMBER

-

Number of dequeue attempts allowed

RETRY_DELAY

NUMBER

-

Number of seconds before retry can be attempted

ENQUEUE_ENABLED

VARCHAR2(7)

-

YES or NO

DEQUEUE_ENABLED

VARCHAR2(7)

-

YES or NO

RETENTION

VARCHAR2(40)

-

Number of seconds message is retained after dequeue

USER_COMMENT

VARCHAR2(50)

-

User comment for the queue

Note:

A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. If a dequeue transaction fails because the server process dies (including ALTERSYSTEMKILLSESSION) or SHUTDOWNABORT on the instance, then RETRY_COUNT is not incremented.

Propagation Schedules in User Schema View

Name of View

USER_QUEUE_SCHEDULES

Purpose

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

Table 9-10 DBA_QUEUE_SCHEDULES View

Column

Datatype

NULL

Description

QNAME

VARCHAR2(30)

NOT NULL

Source queue name

DESTINATION

VARCHAR2(128)

NOT NULL

Destination name, currently limited to be a database link name

START_DATE

DATE

-

Date to start propagation in the default date format

START_TIME

VARCHAR2(8)

-

Time of day to start propagation in HH:MI:SS format

PROPAGATION_WINDOW

NUMBER

-

Duration in seconds for the propagation window

NEXT_TIME

VARCHAR2(200)

-

Function to compute the start of the next propagation window

LATENCY

NUMBER

-

Maximum wait time to propagate a message during the propagation window

SCHEDULE_DISABLED

VARCHAR(1)

-

N if enabled; Y if disabled (schedule will not be executed)

PROCESS_NAME

VARCHAR2(8)

-

Name of Jnnn background process executing this schedule; NULL if not currently executing

SESSION_ID

VARCHAR2(82)

-

Session ID (SID, SERIAL#) of the job executing this schedule; NULL if not currently executing

INSTANCE

NUMBER

-

Real Application Clusters instance number executing this schedule

LAST_RUN_DATE

DATE

-

Date of the last successful execution

LAST_RUN_TIME

VARCHAR2(8)

-

Time of the last successful execution in HH:MI:SS format

CURRENT_START_DATE

DATE

-

Date the current window of this schedule was started

CURRENT_START_TIME

VARCHAR2(8)

-

Time the current window of this schedule was started in HH:MI:SS format

NEXT_RUN_DATE

DATE

-

Date the next window of this schedule will be started

NEXT_RUN_TIME

VARCHAR2(8)

-

Time the next window of this schedule will be started in HH:MI:SS format

TOTAL_TIME

NUMBER

-

Total time in seconds spent in propagating messages from the schedule

TOTAL_NUMBER

NUMBER

-

Total number of messages propagated in this schedule

TOTAL_BYTES

NUMBER

-

Total number of bytes propagated in this schedule

MAX_NUMBER

NUMBER

-

Maximum number of messages propagated in a propagation window

MAX_BYTES

NUMBER

-

Maximum number of bytes propagated in a propagation window

AVG_NUMBER

NUMBER

-

Average number of messages propagated in a propagation window

AVG_SIZE

NUMBER

-

Average size of propagated messages in bytes

AVG_TIME

NUMBER

-

Average time to propagate a message in seconds

FAILURES

NUMBER

-

Number of times execution failed. If it reaches 16, then the schedule is disabled.

LAST_ERROR_DATE

DATE

-

Date of the last unsuccessful execution

LAST_ERROR_TIME

VARCHAR2(8)

-

Time of the last unsuccessful execution in HH:MI:SS format

LAST_ERROR_MSG

VARCHAR2(4000)

-

Error number and error message text of the last unsuccessful execution

Queue Subscribers View

Name of View

AQ$Queue_Table_Name_S

Purpose

This is a view of subscribers for all the queues in any given queue table. The subscriber view 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 is only created for 8.1-compatible queue tables.

Table 9-11 AQ$Queue_Table_Name_S View

Column

Datatype

NULL

Description

QUEUE

VARCHAR2(30)

NOT NULL

Name of queue for which subscriber is defined

NAME

VARCHAR2(30)

-

Name of agent

ADDRESS

VARCHAR2(1024)

-

Address of agent

PROTOCOL

NUMBER

-

Protocol of agent

TRANSFORMATION

VARCHAR2(61)

-

Name of the transformation (can be null)

Usage Notes

For queues created in 8.1-compatible queue tables, 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.

Queue Subscribers and Their Rules View

Name of View

AQ$Queue_Table_Name_R

Purpose

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 only created for 8.1-compatible queue tables.

Table 9-12 AQ$Queue_Table_Name_R View

Column

Datatype

NULL

Description

QUEUE

VARCHAR2(30)

NOT NULL

Name of queue for which subscriber is defined

NAME

VARCHAR2(30)

-

Name of agent

ADDRESS

VARCHAR2(1024)

-

Address of agent

PROTOCOL

NUMBER

-

Protocol of agent

RULE

CLOB

-

Text of defined rule

RULE_SET

VARCHAR2(65)

-

Set of rules

TRANSFORMATION

VARCHAR2(61)

-

Name of the transformation (can be null)

Number of Messages in Different States for the Whole Database View

Name of View

GV$AQ

Purpose

Provides information about the number of messages in different states for the whole database.

Table 9-13 GV$AQ View

Column

Datatype

NULL

Description

QID

NUMBER

-

Identity of the queue (same as QID in user_queues and dba_queues)

WAITING

NUMBER

-

Number of messages in the state WAITING

READY

NUMBER

-

Number of messages in state READY

EXPIRED

NUMBER

-

Number of messages in state EXPIRED

TOTAL_WAIT

NUMBER

-

Number of seconds messages in the queue have been waiting in state READY

AVERAGE_WAIT

NUMBER

-

Average number of seconds messages in state READY have been waiting to be dequeued

Number of Messages in Different States for Specific Instances View

Name of View

V$AQ

Purpose

Provides information about the number of messages in different states for specific instances.

Table 9-14 V$AQ View

Column

Datatype

NULL

Description

QID

NUMBER

-

Identity of the queue (same as QID in user_queues and dba_queues)

WAITING

NUMBER

-

Number of messages in the state WAITING

READY

NUMBER

-

Number of messages in state READY

EXPIRED

NUMBER

-

Number of messages in state EXPIRED

TOTAL_WAIT

NUMBER

-

Number of seconds messages in the queue have been waiting in state READY

AVERAGE_WAIT

NUMBER

-

Average number of seconds messages in state READY have been waiting to be dequeued

Oracle Streams AQ Agents Registered for Internet Access View

Name of View

AQ$INTERNET_USERS

Purpose

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.

Table 9-15 AQ$INTERNET_USERS View

Column

Datatype

NULL

Description

AGENT_NAME

VARCHAR2(30)

-

Name of the Oracle Streams AQ Internet agent

DB_USERNAME

VARCHAR2(30)

-

Name of database user that this Internet agent maps to

HTTP_ENABLED

VARCHAR2(4)

-

Indicates whether this agent is allowed to access Oracle Streams AQ through HTTP (YES or NO)

FTP_ENABLED

VARCHAR2(4)

-

Indicates whether this agent is allowed to access Oracle Streams AQ through FTP (always NO in current release)

All Transformations View

Name of View

DBA_TRANSFORMATIONS

Purpose

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.

Table 9-16 DBA_TRANSFORMATIONS View

Column

Datatype

NULL

Description

TRANSFORMATION_ID

NUMBER

NOT NULL

Unique ID for the transformation

OWNER

VARCHAR2(30)

NOT NULL

Owning user of the transformation

NAME

VARCHAR2(30)

NOT NULL

Transformation name

FROM_TYPE

VARCHAR2(61)

-

Source type name

TO_TYPE

VARCHAR2(91)

-

Target type name

All Transformation Functions View

Name of View

DBA_ATTRIBUTE_TRANSFORMATIONS

Purpose

Displays the transformation functions for all the transformations in the database.

Table 9-17 DBA_ATTRIBUTE_TRANSFORMATIONS View

Column

Datatype

NULL

Description

TRANSFORMATION_ID

NUMBER

NOT NULL

Unique ID for the transformation

OWNER

VARCHAR2(30)

NOT NULL

Transformation owner

NAME

VARCHAR2(30)

NOT NULL

Transformation name

FROM_TYPE

VARCHAR2(61)

-

Source type name

TO_TYPE

VARCHAR2(91)

-

Target type name

ATTRIBUTE

NUMBER

NOT NULL

Target type attribute number

ATTRIBUTE_TRANSFORMATION

VARCHAR2(4000)

-

Transformation function for the attribute

User Transformations View

Name of View

USER_TRANSFORMATIONS

Purpose

Displays all the transformations owned by the user. To view the transformation definition, query USER_ATTRIBUTE_TRANSFORMATIONS.

Table 9-18 USER_TRANSFORMATIONS View

Column

Datatype

NULL

Description

TRANSFORMATION_ID

NUMBER

NOT NULL

Unique ID for the transformation

NAME

VARCHAR2(30)

NOT NULL

Transformation name

FROM_TYPE

VARCHAR2(61)

-

Source type name

TO_TYPE

VARCHAR2(91)

-

Target type name

User Transformation Functions View

Name of View

USER_ATTRIBUTE_TRANSFORMATIONS

Purpose

Displays the transformation functions for all the transformations of the user.