Rem Filename   : queues.sql
Rem 
Rem Parameters : &1 = [owner(pattern).]queue(pattern) 
Rem              The queues that have to be listed.
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.1.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 25-Feb-2004  Intial creation.
Rem 25-Apr-2005  Added report header.
Rem
Rem ============================================================================================================================
Rem
Rem Description : Lists all matching queues.
Rem
Rem               Queue owner          : Owner of the queue.
Rem               Queue name           : Name of the queue.
Rem               Queue type           : Type of the queue.
Rem               Queue table          : Name of the table the queue data resides in.
Rem               Maximum # of retries : Maximum number of retries allowed when dequeuing from the queue.
Rem               Retry delay          : Time interval between retries.
Rem               Enqueue enabled      : "YES" if queue is enabled for enqueue else "NO".
Rem               Dequeue enabled      : "YES" if queue is enabled for dequeue else "NO".
Rem               Retention interval   : Time interval processed messages are retained in the queue.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<RDB>  @queues STRM%.%
Rem
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem                        All queues with owner matching patttern <STRM%> and name matching pattern <%>
Rem
Rem                                            of database RDB1 on server beahs661640
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem Current date and time : Thursday  28-APR-2005 18:35:36
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem
Rem                                                                                    Maximum  Retry Enqueue Dequeue Retention
Rem Queue owner  Queue name                Queue type         Queue table          # of retries  Delay Enabled Enabled Interval
Rem ------------ ------------------------- ------------------ ------------------ -------------- ------ ------- ------- ---------
Rem STRM_ADMIN   AQ$_QU_PDB_TO_RDB_FAST_E  EXCEPTION_QUEUE    QU_PDB_TO_RDB_FAST              0      0   NO      NO    0
Rem STRM_ADMIN   AQ$_QU_PDB_TO_RDB_SLOW_E  EXCEPTION_QUEUE    QU_PDB_TO_RDB_SLOW              0      0   NO      NO    0
Rem STRM_ADMIN   AQ$_QU_RDB_TO_HDB_E       EXCEPTION_QUEUE    QU_RDB_TO_HDB                   0      0   NO      NO    0
Rem STRM_ADMIN   QU_PDB_TO_RDB_FAST        NORMAL_QUEUE       QU_PDB_TO_RDB_FAST              5      0   YES     YES   0
Rem STRM_ADMIN   QU_PDB_TO_RDB_SLOW        NORMAL_QUEUE       QU_PDB_TO_RDB_SLOW              5      0   YES     YES   0
Rem STRM_ADMIN   QU_RDB_TO_HDB             NORMAL_QUEUE       QU_RDB_TO_HDB                   5      0   YES     YES   0
Rem 
Rem ============================================================================================================================

SET PAGESIZE  0
SET LINESIZE 300
SET TRIMSPOOL ON
SET VERIFY    ON
SET FEEDBACK  OFF
SET TERMOUT   OFF

-- Disect the input argument, and get the owner name and queue name into two seperate 
-- substitution variables. The owner name defaults to the current user.

DEFINE InputOwnerFilter  = ' '
DEFINE InputQueueFilter  = ' '
COLUMN queue_owner NOPRINT NEW_VALUE InputOwnerFilter
COLUMN queue_name  NOPRINT NEW_VALUE InputQueueFilter

SELECT decode(instr('&&1','.'), 0, upper('&1'),  /*Default to current user.*/
                                upper(substr('&&1',1,instr('&&1','.')-1))) queue_owner,
       decode(instr('&&1','.'), 0, upper('&2'),  /*Only the queue name was passed in.*/
                                upper(substr('&&1',instr('&&1','.')+1))) queue_name
  FROM dual;

alter session set nls_numeric_characters = ',.';

CLEAR COLUMNS
SET PAGESIZE     9999
SET TERMOUT      ON

@@header_title 124 DC 'All queues with owner matching patttern <&InputOwnerFilter> and name matching pattern <&InputQueueFilter>'

COLUMN Owner       FORMAT A12           HEADING "Queue owner"
COLUMN Name        FORMAT A25           HEADING "Queue name"
COLUMN QueueTable  FORMAT A18           HEADING "Queue table"
COLUMN QueueType   FORMAT A18           HEADING "Queue type"
COLUMN MaxRetries  FORMAT 9G999G999G999 HEADING "Maximum|# of retries"
COLUMN Delay       FORMAT 9G999         HEADING "Retry|Delay"
COLUMN EnqEna      FORMAT A7            HEADING "Enqueue|Enabled"
COLUMN DeqEna      FORMAT A7            HEADING "Dequeue|Enabled"
COLUMN Retention   FORMAT A9            HEADING "Retention|Interval"

SELECT owner            "Owner",
       name             "Name", 
       queue_type       "QueueType",
       queue_table      "QueueTable",
       max_retries      "MaxRetries",
       retry_delay      "Delay",
       enqueue_enabled  "EnqEna",        
       dequeue_enabled  "DeqEna", 
       retention        "Retention"
  FROM dba_queues 
 WHERE owner like '%&InputOwnerFilter%'
   AND name  like '%&InputQueueFilter%'
 ORDER BY owner, name;

UNDEFINE InputOwnerFilter
UNDEFINE InputQueueFilter

SET FEEDBACK ON

PROMPT