Rem Filename   : queuetabs.sql
Rem 
Rem Parameters : &1 = [owner(pattern).]queuetable(pattern) 
Rem              The queue tables that have to be listed.
Rem
Rem Author     :
Rem RDBMS      : Oracle V8.1.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 02-Mar-2004  Intial creation.
Rem 06-May-2004  Added the size of the queue table.
Rem 28-Apr-2005  Added report header
Rem 16-May-2006  Added the tablespace name.
Rem
Rem ==============================================================================================================
Rem
Rem Description : Lists all matching queue tables.
Rem
Rem               owner             : Owner of the queue table.
Rem               Queue table       : Name of the queue table.
Rem               Tablespace        : The tablespace storing the queue table.
Rem               Type              : Type of user data : RAW, OBJECT (user defined), VARIANT (interanal use only).
Rem               Object type       : Object type of the payload when TYPE is OBJECT;
Rem               Recipients        : SINGLE or MULTIPLE recipients.
Rem               Compatible        : Lowest release level which this queue table is
Rem                                   compatible with (for example, 8.0.3).
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<wntchaa4:PDB1>  @queuetabs %.%
Rem
Rem --------------------------------------------------------------------------------------------------------------------------------
Rem                          All queue tables with owner matching patttern <%> and name matching pattern <%>
Rem 
Rem                                                of database PDB1 on server wntchaa4
Rem --------------------------------------------------------------------------------------------------------------------------------
Rem Current date and time : Tuesday   16-MAY-2006 06:56:29
Rem --------------------------------------------------------------------------------------------------------------------------------
Rem 
Rem Owner           Queue table          Tablespace      Recipients Size(Kb) Compatible Type     Object type
Rem --------------- -------------------- --------------- ---------- -------- ---------- -------- -----------------------------------
Rem EFLEX_CORE      CO_AQ_T_COC          AQ_TS           SINGLE           64 8.1.3      OBJECT   EFLEX_CORE.TRG_MSG_T
Rem EFLEX_CORE      CO_AQ_T_EVT          AQ_TS           SINGLE           64 8.1.3      OBJECT   EFLEX_CORE.TRG_EV2_T
Rem EFLEX_CORE      CO_AQ_T_FMT          AQ_TS           SINGLE           64 8.1.3      OBJECT   EFLEX_CORE.TRG_MSG_T
Rem EFLEX_CORE      CO_AQ_T_TRG          AQ_TS           SINGLE           64 8.1.3      OBJECT   EFLEX_CORE.TRG_EVT_T
Rem SYS             AQ_EVENT_TABLE       SYSTEM          SINGLE           64 8.0.3      OBJECT   SYS.AQ$_EVENT_MESSAGE
Rem SYS             AQ_SRVNTFN_TABLE     SYSTEM          SINGLE           64 8.1.3      OBJECT   SYS.AQ$_SRVNTFN_MESSAGE
Rem SYSTEM          DEF$_AQCALL          SYSTEM          SINGLE           64 8.0.3      VARIANT
Rem SYSTEM          DEF$_AQERROR         SYSTEM          SINGLE           64 8.0.3      VARIANT
Rem
Rem ================================================================================================================

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

-- Disect the input argument, and get the owner name and table 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 128 DC 'All queue tables with owner matching patttern <&InputOwnerFilter> and name matching pattern <&InputQueueFilter>'

COLUMN Owner       FORMAT A15           HEADING "Owner"
COLUMN QueueTable  FORMAT A20           HEADING "Queue table"
COLUMN Ts          FORMAT A15           HEADING "Tablespace"
COLUMN SizeKb      FORMAT 999G999       HEADING "Size(Kb)"
COLUMN Recipients  FORMAT A10           HEADING "Recipients"
COLUMN Compatible  FORMAT A10           HEADING "Compatible"
COLUMN Type        FORMAT A8            HEADING "Type"
COLUMN ObjType     FORMAT A35           HEADING "Object type"

SELECT dba_queue_tables.owner   "Owner",
       queue_table              "QueueTable",
       segm.tablespace_name     "Ts",
       recipients               "Recipients",
       bytes/1024               "SizeKb",
       compatible               "Compatible",
       type                     "Type",
       object_type              "ObjType"
  FROM dba_queue_tables,
       dba_segments segm
 WHERE dba_queue_tables.owner like '%&InputOwnerFilter%'
   AND queue_table like '%&InputQueueFilter%'
   AND segm.segment_name = dba_queue_tables.queue_table
   AND segm.owner = dba_queue_tables.owner
 ORDER BY dba_queue_tables.owner, queue_table;

UNDEFINE InputOwnerFilter
UNDEFINE InputQueueFilter

SET FEEDBACK ON

PROMPT