Oracle Select From Replication Queue? Oracle Database Tips by Donald Burleson

Question: 

    How do I Oracle select from replication queue?

Answer:

Good question.  Oracle replication (Streams, multimaster, snapshot) was never intended to have a mechanism to reveal the internal tables, but the DBA always needs to see this level of detail to diagnose replication problems.

Oracle snapshots create a table named as "snap$_snapshotname", and you might be able to see transient rows by selecting from this table, understanding that snap$tablename SQL queries are useful only for DBA diagnostics.  We also see tables prefixes with "rep$" for Oracle 10g replication using advanced queuing and the dbms_aqadm management packages, with procedures for managing queue tables. 

More on Replication queue tables

There are two authors on books relating solely to Oracle Replication, John Garmany (Author of Oracle Replication book) and Madhu Tumma (Author of the Oracle Streams book).  If you want to understand the internals of Oracle replications, I recommend these tight-focus reference books:

• Oracle Streams Book
• Oracle networking & multi-master replication book
• Oracle PL/SQL Programming book

Also see:

• Oracle dbms_aqadm example
• Oracle dbms_aq
• The Publish/Subscribe Model for Oracle Replication

The superb book "Oracle PL/SQL Programming" describes the procedures of dbms_aqadm:

The ENQUEUE procedure

The ENQUEUE procedure adds a message to an existing message queue. The target message queue must have had enqueuing enabled previously via the DBMS_ AQADM.START_QUEUE procedure.

The DEQUEUE procedure

The DEQUEUE procedure can either remove or browse a message from an existing message queue. The target message queue must have had dequeuing enabled previously via the STOP_QUEUE procedure.

The CREATE_QUEUE_TABLE procedure

The CREATE_QUEUE_TABLE procedure creates a queue table. A queue table is the named repository for a set of queues and their messages. A queue table may contain numerous queues, each of which may have many messages. But a given queue and its messages may exist in only one queue table.

The DROP_QUEUE_TABLE procedure

The DROP_QUEUE_TABLE procedure drops an existing queue table. An error is returned if the queue table does not exist. The force parameter specifies whether all existing queues in the queue table are stopped and dropped automatically or manually.

The CREATE_QUEUE procedure

The CREATE_QUEUE procedure creates a new message queue within an existing queue table. An error is returned if the queue table does not exist. The required queue_name parameter specifies the name of the new message queue to create. All queue names must be unique within the schema.

C.3.2.4 The ALTER_QUEUE procedure

The ALTER_QUEUE procedure modifies properties of an existing message queue. It returns an error if the message queue does not exist. Currently, you can alter only the maximum retries, retry delay, retention time, rentention delay and auto-commit properties; Oracle will augment this list in future releases.

The DROP_QUEUE procedure

The DROP_QUEUE procedure drops an existing message queue. It returns an error if the message queue does not exist. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable both enqueuing and dequeuing for the message queue to be dropped. If the message queue has not been stopped, then DROP_QUEUE returns an error of queue resource busy.

The START_QUEUE procedure

The START_QUEUE procedure enables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to enable both.

The STOP_QUEUE procedure

The STOP_QUEUE procedure disables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to disable both enqueuing and dequeuing. The wait parameter specifies whether to wait for outstanding transactions or to return immediately. The wait option is highly dependent on outstanding transactions. If outstanding transactions exist, then wait will either hang until the transactions complete or return an error of ORA-24203, depending on whether the wait parameter is set to true or false.

---

If you like tuning, see my new book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.