Lesson 3 | Data dictionary views for Advanced Queuing |
Objective | Describe important Data Dictionary Views that contain information about Advanced Queuing |
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:
- an
ALL_
view, where all available entities are shown;
- a
USER_
view, which shows only entities in the current user's schema; and
- 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:
NAME
: The name of the queue
QUEUE_TABLE
: The queue table that holds the queue
ENQUEUE_ENABLED
: Whether a queue can allow messages to be enqueued
DEQUEUE_ENABLED
: Whether a queue can allow messages to be dequeued
RETENTION
: The amount of time a message is retained in the queue
Other static views
The following two views have
DBA_
and
USER_
versions:
QUEUE_SCHEDULES
: The messages in a queue can be propagated to other queues according to a schedule. This view provides information about propagation schedules.
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.
- 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.
- Sequences: Provide unique numbers for column values.
- Stored procedures: Contain logical modules that can be called from programs.
- Synonyms: Provide alternative names for database objects.
- Indexes: Provide faster access to table rows.
- Database links: Provide links between distributed databases.
Advanced Queuing Data Dictionary Views
In the next lesson, you will learn about the PL/SQL packages used in Advanced Queuing.