eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS._DBA_STREAMS_COMPONENT

Source

View Text - Preformatted


SELECT v.COMPONENT_NAME,
       v.COMPONENT_DB,
       v.COMPONENT_TYPE,
       v.COMPONENT_PROPERTY,
       v.COMPONENT_CHANGED_TIME,
       0, 0, NULL, to_date(NULL, '')
FROM (
   SELECT caq.COMPONENT_NAME,
          global_name                       as COMPONENT_DB,
          caq.COMPONENT_TYPE,
          caq.COMPONENT_PROPERTY,
          o.mtime                           as COMPONENT_CHANGED_TIME
   FROM (
-- Capture
      SELECT c.capture_name                 as COMPONENT_NAME,
             1                              as COMPONENT_TYPE,
             decode(bitand(c.flags, 64), 64,
-- Downstream 0001; Downstream Hotmining 0101; Downstream Coldmining 1001
                    decode(p.value, 'Y', 5, 'N', 9, null, 0, 1),
-- Local 0010; Local Hotmining 0110; Local Coldmining 1010; Unknown 0000
                    decode(p.value, 'Y', 6, 'N',10, null, 0, 2))
                                            as COMPONENT_PROPERTY,
             'SYS'                          as OBJECT_OWNER,
             c.capture_name                 as OBJECT_NAME
      -- OPTIMIZE: Replace dba_capture with sys.streams$_capture_process
      -- and dba_capture_parameters with sys.streams$_process_params to
      -- optimize query performance.
      FROM sys.streams$_capture_process c, sys.streams$_process_params p
      WHERE p.process_type = 2 AND   -- type 2 indicates capture process
            p.process# = c.capture# AND
      -- For local and downstream capture, 'DOWNSTREAM_REAL_TIME_MINE'
      -- is always populated.
            p.name = 'DOWNSTREAM_REAL_TIME_MINE'
      UNION
-- Apply/Extract
      SELECT decode(purpose,
                    'GoldenGate Capture', ltrim(apply_name, 'OGG$'),
                    apply_name)             as COMPONENT_NAME,
             decode(purpose, 'GoldenGate Capture', 6, 4)
                                            as COMPONENT_TYPE,
             0                              as COMPONENT_PROPERTY,
             'SYS'                          as OBJECT_OWNER,
             apply_name                     as OBJECT_NAME
     FROM dba_apply
     UNION
-- Replicat -- counting Apply component also as Replicat component
     SELECT decode(purpose,
                    'GoldenGate Apply', ltrim(apply_name, 'OGG$'),
                    apply_name)             as COMPONENT_NAME,
             decode(purpose, 'GoldenGate Apply', 7, 4)
                                            as COMPONENT_TYPE,
             0                              as COMPONENT_PROPERTY,
             'SYS'                          as OBJECT_OWNER,
             apply_name                     as OBJECT_NAME
      FROM dba_apply
      UNION
-- Queue
-- Every queue in 'gv$buffered_queues' is buffered. Otherwise, persistent.
      SELECT ('"'||q.queue_schema||'"."'||q.queue_name||'"')
                                            as COMPONENT_NAME,
             5                              as COMPONENT_TYPE,
             16                             as COMPONENT_PROPERTY,
             q.queue_schema                 as OBJECT_OWNER,
             q.queue_name                   as OBJECT_NAME
      FROM   gv$buffered_queues q
      UNION
      SELECT ('"'||t.schema||'"."'||q.name||'"')
                                            as COMPONENT_NAME,
             5                              as COMPONENT_TYPE,
             32                             as COMPONENT_PROPERTY,
             t.schema                       as OBJECT_OWNER,
             q.name                         as OBJECT_NAME
      FROM   system.aq$_queues q,
             system.aq$_queue_tables t
      WHERE  q.table_objno = t.objno AND
-- Use system.aq$_queues.usage to find 'NORMAL_QUEUE' in dba_queues
             q.usage NOT IN (1, 2) AND
             q.eventid NOT IN (SELECT queue_id FROM gv$buffered_queues)
         ) caq, sys.obj$ o, sys.user$ u, global_name
   -- OPTIMIZE: Replace dba_objects with sys.obj$ and sys.user$
   --           and extract global_name
   WHERE caq.object_owner = u.name AND
         caq.object_name = o.name AND
         o.owner# = u.user# AND
-- namespace values for queue, capture and apply are 10, 37 and 39 respectively
         o.namespace in (10, 37, 39)
   UNION
-- Propagation Sender
-- Using sys.streams$_propagation_process instead of gv$propagation_sender,
-- we can have propagation sender even when dst_database_name is missing in
-- gv$propagation_sender. We can have creation_time as component_changed_time.
   SELECT ('"'||source_queue_schema||'"."'||source_queue||
           '"=>"'||destination_queue_schema||'"."'||destination_queue||
           '"@'||destination_dblink)     as COMPONENT_NAME,
          global_name                    as COMPONENT_DB,
          2                              as COMPONENT_TYPE,
          0                              as COMPONENT_PROPERTY,
-- The creation time of propagation is the last ddl time.
          creation_time                  as COMPONENT_CHANGED_TIME
   FROM sys.streams$_propagation_process, global_name
   UNION
-- Propagation Receiver
-- Using sys.streams$_propagation_process, we can always produce
-- propagation receiver, even when gv$propagation_receiver is missing.
-- NOTE: This streams component is stored on the source database though
-- it physically resides on the destination database.
   SELECT ('"'||source_queue_schema||'"."'||source_queue||
           '"@'||global_name||'=>"'||destination_queue_schema||'"."'||
           destination_queue||'"')       as COMPONENT_NAME,
          destination_dblink             as COMPONENT_DB,
          3                              as COMPONENT_TYPE,
          0                              as COMPONENT_PROPERTY,
          to_date(null, '')              as COMPONENT_CHANGED_TIME
   -- OPTIMIZE: Replace dba_propagation with sys.streams$_propagation_process
   FROM sys.streams$_propagation_process, global_name
   WHERE destination_dblink IS NOT NULL
   UNION
-- Propagation Receiver in case of XStreamIn, using xstream$_server.
-- The component name is formatted as:
--     "SOURCE_NAME"=>"QUEUE_SCHEMA"."QUEUE_NAME"
   SELECT ('"'||xs.cap_src_database||'"=>"'||
           xs.queue_owner||'"."'||xs.queue_name||'"')
                                        as COMPONENT_NAME,
          global_name                   as COMPONENT_DB,
          3                             as COMPONENT_TYPE,
          0                             as COMPONENT_PROPERTY,
          xs.create_date                as COMPONENT_CHANGED_TIME
   FROM xstream$_server xs, global_name
   WHERE BITAND(xs.flags, 2) = 2 and SUBSTR(server_name, 1, 4) != 'OGG$'
   ) v

View Text - HTML Formatted

SELECT V.COMPONENT_NAME
, V.COMPONENT_DB
, V.COMPONENT_TYPE
, V.COMPONENT_PROPERTY
, V.COMPONENT_CHANGED_TIME
, 0
, 0
, NULL
, TO_DATE(NULL
, '') FROM ( SELECT CAQ.COMPONENT_NAME
, GLOBAL_NAME AS COMPONENT_DB
, CAQ.COMPONENT_TYPE
, CAQ.COMPONENT_PROPERTY
, O.MTIME AS COMPONENT_CHANGED_TIME
FROM ( -- CAPTURE SELECT C.CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, DECODE(BITAND(C.FLAGS
, 64)
, 64
, -- DOWNSTREAM 0001; DOWNSTREAM HOTMINING 0101; DOWNSTREAM COLDMINING 1001 DECODE(P.VALUE
, 'Y'
, 5
, 'N'
, 9
, NULL
, 0
, 1)
, -- LOCAL 0010; LOCAL HOTMINING 0110; LOCAL COLDMINING 1010; UNKNOWN 0000 DECODE(P.VALUE
, 'Y'
, 6
, 'N'
, 10
, NULL
, 0
, 2)) AS COMPONENT_PROPERTY
, 'SYS' AS OBJECT_OWNER
, C.CAPTURE_NAME AS OBJECT_NAME -- OPTIMIZE: REPLACE DBA_CAPTURE WITH SYS.STREAMS$_CAPTURE_PROCESS --
AND DBA_CAPTURE_PARAMETERS WITH SYS.STREAMS$_PROCESS_PARAMS TO -- OPTIMIZE QUERY PERFORMANCE.
FROM SYS.STREAMS$_CAPTURE_PROCESS C
, SYS.STREAMS$_PROCESS_PARAMS P
WHERE P.PROCESS_TYPE = 2
AND -- TYPE 2 INDICATES CAPTURE PROCESS P.PROCESS# = C.CAPTURE# AND -- FOR LOCAL
AND DOWNSTREAM CAPTURE
, 'DOWNSTREAM_REAL_TIME_MINE' -- IS ALWAYS POPULATED. P.NAME = 'DOWNSTREAM_REAL_TIME_MINE' UNION -- APPLY/EXTRACT SELECT DECODE(PURPOSE
, 'GOLDENGATE CAPTURE'
, LTRIM(APPLY_NAME
, 'OGG$')
, APPLY_NAME) AS COMPONENT_NAME
, DECODE(PURPOSE
, 'GOLDENGATE CAPTURE'
, 6
, 4) AS COMPONENT_TYPE
, 0 AS COMPONENT_PROPERTY
, 'SYS' AS OBJECT_OWNER
, APPLY_NAME AS OBJECT_NAME
FROM DBA_APPLY UNION -- REPLICAT -- COUNTING APPLY COMPONENT ALSO AS REPLICAT COMPONENT SELECT DECODE(PURPOSE
, 'GOLDENGATE APPLY'
, LTRIM(APPLY_NAME
, 'OGG$')
, APPLY_NAME) AS COMPONENT_NAME
, DECODE(PURPOSE
, 'GOLDENGATE APPLY'
, 7
, 4) AS COMPONENT_TYPE
, 0 AS COMPONENT_PROPERTY
, 'SYS' AS OBJECT_OWNER
, APPLY_NAME AS OBJECT_NAME
FROM DBA_APPLY UNION -- QUEUE -- EVERY QUEUE IN 'GV$BUFFERED_QUEUES' IS BUFFERED. OTHERWISE
, PERSISTENT. SELECT ('"'||Q.QUEUE_SCHEMA||'"."'||Q.QUEUE_NAME||'"') AS COMPONENT_NAME
, 5 AS COMPONENT_TYPE
, 16 AS COMPONENT_PROPERTY
, Q.QUEUE_SCHEMA AS OBJECT_OWNER
, Q.QUEUE_NAME AS OBJECT_NAME
FROM GV$BUFFERED_QUEUES Q UNION SELECT ('"'||T.SCHEMA||'"."'||Q.NAME||'"') AS COMPONENT_NAME
, 5 AS COMPONENT_TYPE
, 32 AS COMPONENT_PROPERTY
, T.SCHEMA AS OBJECT_OWNER
, Q.NAME AS OBJECT_NAME
FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
WHERE Q.TABLE_OBJNO = T.OBJNO AND -- USE SYSTEM.AQ$_QUEUES.USAGE TO FIND 'NORMAL_QUEUE' IN DBA_QUEUES Q.USAGE NOT IN (1
, 2) AND Q.EVENTID NOT IN (SELECT QUEUE_ID
FROM GV$BUFFERED_QUEUES) ) CAQ
, SYS.OBJ$ O
, SYS.USER$ U
, GLOBAL_NAME -- OPTIMIZE: REPLACE DBA_OBJECTS WITH SYS.OBJ$
AND SYS.USER$ --
AND EXTRACT GLOBAL_NAME
WHERE CAQ.OBJECT_OWNER = U.NAME AND CAQ.OBJECT_NAME = O.NAME AND O.OWNER# = U.USER# AND -- NAMESPACE VALUES FOR QUEUE
, CAPTURE
AND APPLY ARE 10
, 37
AND 39 RESPECTIVELY O.NAMESPACE IN (10
, 37
, 39) UNION -- PROPAGATION SENDER -- USING SYS.STREAMS$_PROPAGATION_PROCESS INSTEAD OF GV$PROPAGATION_SENDER
, -- WE CAN HAVE PROPAGATION SENDER EVEN WHEN DST_DATABASE_NAME IS MISSING IN -- GV$PROPAGATION_SENDER. WE CAN HAVE CREATION_TIME AS COMPONENT_CHANGED_TIME. SELECT ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE|| '"=>"'||DESTINATION_QUEUE_SCHEMA||'"."'||DESTINATION_QUEUE|| '"@'||DESTINATION_DBLINK) AS COMPONENT_NAME
, GLOBAL_NAME AS COMPONENT_DB
, 2 AS COMPONENT_TYPE
, 0 AS COMPONENT_PROPERTY
, -- THE CREATION TIME OF PROPAGATION IS THE LAST DDL TIME. CREATION_TIME AS COMPONENT_CHANGED_TIME
FROM SYS.STREAMS$_PROPAGATION_PROCESS
, GLOBAL_NAME UNION -- PROPAGATION RECEIVER -- USING SYS.STREAMS$_PROPAGATION_PROCESS
, WE CAN ALWAYS PRODUCE -- PROPAGATION RECEIVER
, EVEN WHEN GV$PROPAGATION_RECEIVER IS MISSING. -- NOTE: THIS STREAMS COMPONENT IS STORED ON THE SOURCE DATABASE THOUGH -- IT PHYSICALLY RESIDES ON THE DESTINATION DATABASE. SELECT ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE|| '"@'||GLOBAL_NAME||'=>"'||DESTINATION_QUEUE_SCHEMA||'"."'|| DESTINATION_QUEUE||'"') AS COMPONENT_NAME
, DESTINATION_DBLINK AS COMPONENT_DB
, 3 AS COMPONENT_TYPE
, 0 AS COMPONENT_PROPERTY
, TO_DATE(NULL
, '') AS COMPONENT_CHANGED_TIME -- OPTIMIZE: REPLACE DBA_PROPAGATION WITH SYS.STREAMS$_PROPAGATION_PROCESS
FROM SYS.STREAMS$_PROPAGATION_PROCESS
, GLOBAL_NAME
WHERE DESTINATION_DBLINK IS NOT NULL UNION -- PROPAGATION RECEIVER IN CASE OF XSTREAMIN
, USING XSTREAM$_SERVER. -- THE COMPONENT NAME IS FORMATTED AS: -- "SOURCE_NAME"=>"QUEUE_SCHEMA"."QUEUE_NAME" SELECT ('"'||XS.CAP_SRC_DATABASE||'"=>"'|| XS.QUEUE_OWNER||'"."'||XS.QUEUE_NAME||'"') AS COMPONENT_NAME
, GLOBAL_NAME AS COMPONENT_DB
, 3 AS COMPONENT_TYPE
, 0 AS COMPONENT_PROPERTY
, XS.CREATE_DATE AS COMPONENT_CHANGED_TIME
FROM XSTREAM$_SERVER XS
, GLOBAL_NAME
WHERE BITAND(XS.FLAGS
, 2) = 2
AND SUBSTR(SERVER_NAME
, 1
, 4) != 'OGG$' ) V