Oracle Data Dictionary Views

Because queues reside in the Oracle database, a number of data dictionary views shape the operation of queues and give you information on the queues. For some of these views, there are three versions:

1. an ALL_ view, where all available entities are shown;
2. a USER_ view, which shows only entities in the current user's schema; and
3. a DBA_ view, which shows entities available to a user with DBA_ privileges.

To access the ALL_ or DBA_ versions, a user must have the appropriate privileges.

• QUEUES Views
  The QUEUES data dictionary views, ALL_QUEUES, USER_QUEUES, and DBA_QUEUES, display information about a queue, including:
  1. NAME: The name of the queue
  2. QUEUE_TABLE: The queue table that holds the queue
  3. ENQUEUE_ENABLED: Whether a queue can allow messages to be enqueued
  4. DEQUEUE_ENABLED: Whether a queue can allow messages to be dequeued
  5. RETENTION: The amount of time a message is retained in the queue

The following two views have DBA_ and USER_ versions:

1. QUEUE_SCHEDULES: The messages in a queue can be propagated to other queues according to a schedule. This view provides information about propagation schedules.
2. QUEUE_PRIVILEGES: Shows the queuing privileges granted to the current session.

• Dynamic Views
  In addition to the static data dictionary views described above, there is a dynamic view for queues that gives you access to current statistics and performance information about queues. The V$ AQ view gives statistics for current activity in the view, such as the number of messages that are waiting or have expired and the total and average wait time that messages are in the queue. There are two flavors of this view. One flavor has the V prefix, as in V$ AQ, which gives all the information for a single instance of Oracle; the other flavor has a GV prefix, as in GV$ AQ, which gives information for all instances in a parallel server environment.

1. Views: Provide a single view of data derived from one or more tables or views. The view is an alternative interface to the data, which is stored in the underlying table(s) that make up the view.
2. Sequences: Provide unique numbers for column values.
3. Stored procedures: Contain logical modules that can be called from programs.
4. Synonyms: Provide alternative names for database objects.
5. Indexes: Provide faster access to table rows.
6. Database links: Provide links between distributed databases.

The following list contains data dictionary views about Advanced Queuing with their corresponding information.

1. USER_QUEUES: Information about queues in a user's schema
2. DBA_QUEUES: Information about all queues in the database
3. USER_QUEUE_SCHEDULES: Information about propagation of messages in queue
4. USER_QUEUE_PRIVILEGES: Information about queuing privileges for the current user
5. V$AQ: Dynamic information about queues, such as number of messages of various types in queue

The available preference classes, their attributes, and the valid values are described in data dictionary views; these are:

CTX_CLASSES
CTX_OBJECTS
CTX_OBJECT_ATTRIBUTES
CTX_OBJECT_ATTRIBUTE_LOV
CTX_PREFERENCES
CTX_PREFERENCE_VALUES
CTX_USER_INDEX_OBJECTS
CTX_USER_INDEX_VALUES

The first three in the above list each contain a description field explaining their contents. The CTX_DDL package also contains procedures for deleting objects. These are DROP_PREFERENCE, DROP_SECTION_GROUP, deleting from the interMedia Text data dictionary, and UNSET_ATTRIBUTE, which removes an attribute from a preference object. Since some attributes have default values, and some are by default blank, it would be clearer in your code to explicitly re-set attributes on objects that will continue in use.

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.
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.
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.
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.
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.
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.
7. DBA_QUEUE_SCHEDULES: All Propagation Schedules The DBA_QUEUE_SCHEDULES view describes all the current schedules in the database for propagating messages.
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. 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.
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 the next lesson, you will learn about the PL/SQL packages used in Advanced Queuing.