Dqueue (dqueue.sql)

What This Script Does

This script displays Oracle Advanced Queuing (AQ) configuration details, including retry settings, enable/disable status, and retention policies. Essential for understanding queue setup and troubleshooting message flow issues.

The Script

rem  dqueue.sql
rem
set linesize 300
rem
ttitle 'DBA Queue Tables View'
rem
clear break
rem
col OWNER                  format a15
col NAME                   format a25    heading 'Name'
col QUEUE_TABLE            format a25    heading 'Queue|Table'
col QID                    format 99999
col QUEUE_TYPE             format a20    heading 'Queue|Type'
col MAX_RETRIES            format 9,999  heading 'Max|Retry'
col RETRY_DELAY            format 9,999  heading 'Retry|Delay'
col ENQUEUE_ENABLED        format a5     heading 'ENQ|ENABLE'
col DEQUEUE_ENABLED        format a5     heading 'DEQ|ENABLE'
col RETENTION              format a10    heading 'Retention'
col USER_COMMENT           format a20
col NETWORK_NAME           format a50
rem
select  q.OWNER
       ,q.NAME
       ,q.QUEUE_TABLE
       ,q.QID
       ,q.QUEUE_TYPE
       ,q.MAX_RETRIES
       ,q.RETRY_DELAY
       ,q.ENQUEUE_ENABLED
       ,q.DEQUEUE_ENABLED
       ,q.RETENTION
       ,q.USER_COMMENT
--       ,q.NETWORK_NAME
  from dba_queues q
 where nvl(upper(q.owner),'null') like upper(nvl('&owner', '%'))
   and nvl(upper(q.name),'null') like upper(nvl('&name', '%'))
   and nvl(upper(q.queue_table),'null') like upper(nvl('&queue_tbl', '%'))
 order by q.owner, q.queue_table
;
rem
rem set linesize 80

Parameters

The script prompts for:

&Queue - owner to filter (use % for all)
&Queue - name pattern to filter (use % for all)
&Queue - table pattern to filter (use % for all)

Usage

-- Basic usage
@dqueue.sql

-- When prompted, enter:
-- owner: Queue owner to filter (use % for all)
-- name: Queue name pattern to filter (use % for all)
-- queue_tbl: Queue table pattern to filter (use % for all)

Sample Output

DBA Queue Tables View

                                      Queue                                     Queue            Max  Retry ENQ   DEQ
OWNER           Name                      Table                    QID         Type            Retry  Delay ENABLE ENABLE Retention  USER_COMMENT
--------------- ------------------------- ------------------------- ----- -------------------- ----- ------ ----- ------ ---------- --------------------
APPS            EVENT_QUEUE               EVENT_QUEUE_TAB           12345 NORMAL_QUEUE             5     60 YES   YES    0          Event processing
APPS            EVENT_QUEUE_E             EVENT_QUEUE_TAB           12346 EXCEPTION_QUEUE          0      0 YES   YES    0          Event exceptions
APPS            NOTIFICATION_QUEUE        NOTIFICATION_TAB          12347 NORMAL_QUEUE             3    120 YES   YES    86400      User notifications
APPS            NOTIFICATION_QUEUE_E      NOTIFICATION_TAB          12348 EXCEPTION_QUEUE          0      0 YES   YES    86400      Notification errors

HR_STREAMS      APPLY_QUEUE               APPLY_QUEUE_TABLE         23456 NORMAL_QUEUE             5     30 YES   YES    0          Streams apply
HR_STREAMS      APPLY_QUEUE_E             APPLY_QUEUE_TABLE         23457 EXCEPTION_QUEUE          0      0 NO    YES    0          Apply exceptions
HR_STREAMS      CAPTURE_QUEUE             CAPTURE_QUEUE_TABLE       23458 NORMAL_QUEUE             0      0 YES   YES    3600       Streams capture

SYS             AQ$_MEM_MC                AQ$_MEM_MC_QT             78901 NORMAL_QUEUE             5      0 NO    NO     0          Memory channel
SYS             SYS$SERVICE_METRICS       SYS$SERVICE_METRICS_TAB   78902 NORMAL_QUEUE             0      0 NO    NO     0          Service metrics