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_LINK

Source

View Text - Preformatted


SELECT v.SOURCE_COMPONENT_NAME,
       v.SOURCE_COMPONENT_DB,
       v.SOURCE_COMPONENT_TYPE,
       v.DEST_COMPONENT_NAME,
       v.DEST_COMPONENT_DB,
       v.DEST_COMPONENT_TYPE,
       0                        as SPARE1,
       0                        as SPARE2,
       NULL                     as SPARE3,
       to_date(NULL, '')        as SPARE4
FROM (
-- CAPTURE -> QUEUE
   SELECT
     c.capture_name             as SOURCE_COMPONENT_NAME,
     global_name                as SOURCE_COMPONENT_DB,
     1                          as SOURCE_COMPONENT_TYPE,
     ('"'||c.queue_owner||'"."'||c.queue_name||'"')
                                as DEST_COMPONENT_NAME,
     global_name                as DEST_COMPONENT_DB,
     5                          as DEST_COMPONENT_TYPE
   -- OPTIMIZE: Replace dba_capture with sys.streams$_capture_process
   FROM sys.streams$_capture_process c, global_name
   UNION
-- QUEUE -> PROPAGATION SENDER
   SELECT
     ('"'||source_queue_schema||'"."'||source_queue||'"')
                                as SOURCE_COMPONENT_NAME,
     global_name                as SOURCE_COMPONENT_DB,
     5                          as SOURCE_COMPONENT_TYPE,
     ('"'||source_queue_schema||'"."'||source_queue||
      '"=>"'||destination_queue_schema||'"."'||destination_queue||
      '"@'||destination_dblink) as DEST_COMPONENT_NAME,
     global_name                as DEST_COMPONENT_DB,
     2                          as DEST_COMPONENT_TYPE
   FROM sys.streams$_propagation_process, global_name
   UNION
-- PROPAGATION SENDER -> PROPAGATION RECEIVER
   SELECT
     ('"'||source_queue_schema||'"."'||source_queue||
      '"=>"'||destination_queue_schema||'"."'||destination_queue||
      '"@'||destination_dblink) as SOURCE_COMPONENT_NAME,
     global_name                as SOURCE_COMPONENT_DB,
     2                          as SOURCE_COMPONENT_TYPE,
     ('"'||source_queue_schema||'"."'||source_queue||
      '"@'||global_name||'=>"'||destination_queue_schema||'"."'||
      destination_queue||'"')   as DEST_COMPONENT_NAME,
     destination_dblink         as DEST_COMPONENT_DB,
     3                          as DEST_COMPONENT_TYPE
   -- OPTIMIZE: Replace dba_propagation with sys.streams$_propagation_process
   FROM sys.streams$_propagation_process, global_name
   UNION
-- PROPAGATION RECEIVER -> QUEUE
-- NOTE: This link 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 SOURCE_COMPONENT_NAME,
     destination_dblink         as SOURCE_COMPONENT_DB,
     3                          as SOURCE_COMPONENT_TYPE,
     ('"'||destination_queue_schema||'"."'||destination_queue||'"')
                                as DEST_COMPONENT_NAME,
     destination_dblink         as DEST_COMPONENT_DB,
     5                          as DEST_COMPONENT_TYPE
   -- 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 -> QUEUE in case of XStreamIn
-- XStream inbound server is presented as PROPAGATION RECEIVER and we need
-- to add a link from propagation receiver to queue
   SELECT ('"'||xs.cap_src_database||'"=>"'||
            xs.queue_owner||'"."'||xs.queue_name||'"')
                                     as SOURCE_COMPONENT_NAME,
          global_name                as SOURCE_COMPONENT_DB,
          3                          as SOURCE_COMPONENT_TYPE,
          ('"'||xs.queue_owner||'"."'||xs.queue_name||'"')
                                     as DEST_COMPONENT_NAME,
          global_name                as DEST_COMPONENT_DB,
          5                          as DEST_COMPONENT_TYPE
   FROM xstream$_server xs, global_name
   WHERE BITAND(xs.flags, 2) = 2 and SUBSTR(xs.server_name,1, 4) != 'OGG$'
   UNION
-- Replicat -> QUEUE
   SELECT LTRIM(apply_name, 'OGG$')
                                     as SOURCE_COMPONENT_NAME,
          global_name                as SOURCE_COMPONENT_DB,
          7                          as SOURCE_COMPONENT_TYPE,
          ('"'||queue_owner||'"."'||queue_name||'"')
                                     as DEST_COMPONENT_NAME,
          global_name                as DEST_COMPONENT_DB,
          5                          as DEST_COMPONENT_TYPE
   FROM dba_apply, global_name
   WHERE purpose = 'GoldenGate Apply'
   UNION
-- QUEUE -> APPLY/EXTRACT
   SELECT
     ('"'||a.queue_owner||'"."'||a.queue_name||'"')
                                as SOURCE_COMPONENT_NAME,
     global_name                as SOURCE_COMPONENT_DB,
     5                          as SOURCE_COMPONENT_TYPE,
     decode(a.purpose, 'GoldenGate Capture',
            ltrim(a.apply_name, 'OGG$'), a.apply_name)
                                as DEST_COMPONENT_NAME,
     global_name                as DEST_COMPONENT_DB,
     decode(a.purpose, 'GoldenGate Capture', 6, 4)
                                as DEST_COMPONENT_TYPE
   FROM  dba_apply a, global_name ) v

View Text - HTML Formatted

SELECT V.SOURCE_COMPONENT_NAME
, V.SOURCE_COMPONENT_DB
, V.SOURCE_COMPONENT_TYPE
, V.DEST_COMPONENT_NAME
, V.DEST_COMPONENT_DB
, V.DEST_COMPONENT_TYPE
, 0 AS SPARE1
, 0 AS SPARE2
, NULL AS SPARE3
, TO_DATE(NULL
, '') AS SPARE4 FROM ( -- CAPTURE -> QUEUE SELECT C.CAPTURE_NAME AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 1 AS SOURCE_COMPONENT_TYPE
, ('"'||C.QUEUE_OWNER||'"."'||C.QUEUE_NAME||'"') AS DEST_COMPONENT_NAME
, GLOBAL_NAME AS DEST_COMPONENT_DB
, 5 AS DEST_COMPONENT_TYPE -- OPTIMIZE: REPLACE DBA_CAPTURE WITH SYS.STREAMS$_CAPTURE_PROCESS
FROM SYS.STREAMS$_CAPTURE_PROCESS C
, GLOBAL_NAME UNION -- QUEUE -> PROPAGATION SENDER SELECT ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE||'"') AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 5 AS SOURCE_COMPONENT_TYPE
, ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE|| '"=>"'||DESTINATION_QUEUE_SCHEMA||'"."'||DESTINATION_QUEUE|| '"@'||DESTINATION_DBLINK) AS DEST_COMPONENT_NAME
, GLOBAL_NAME AS DEST_COMPONENT_DB
, 2 AS DEST_COMPONENT_TYPE
FROM SYS.STREAMS$_PROPAGATION_PROCESS
, GLOBAL_NAME UNION -- PROPAGATION SENDER -> PROPAGATION RECEIVER SELECT ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE|| '"=>"'||DESTINATION_QUEUE_SCHEMA||'"."'||DESTINATION_QUEUE|| '"@'||DESTINATION_DBLINK) AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 2 AS SOURCE_COMPONENT_TYPE
, ('"'||SOURCE_QUEUE_SCHEMA||'"."'||SOURCE_QUEUE|| '"@'||GLOBAL_NAME||'=>"'||DESTINATION_QUEUE_SCHEMA||'"."'|| DESTINATION_QUEUE||'"') AS DEST_COMPONENT_NAME
, DESTINATION_DBLINK AS DEST_COMPONENT_DB
, 3 AS DEST_COMPONENT_TYPE -- OPTIMIZE: REPLACE DBA_PROPAGATION WITH SYS.STREAMS$_PROPAGATION_PROCESS
FROM SYS.STREAMS$_PROPAGATION_PROCESS
, GLOBAL_NAME UNION -- PROPAGATION RECEIVER -> QUEUE -- NOTE: THIS LINK 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 SOURCE_COMPONENT_NAME
, DESTINATION_DBLINK AS SOURCE_COMPONENT_DB
, 3 AS SOURCE_COMPONENT_TYPE
, ('"'||DESTINATION_QUEUE_SCHEMA||'"."'||DESTINATION_QUEUE||'"') AS DEST_COMPONENT_NAME
, DESTINATION_DBLINK AS DEST_COMPONENT_DB
, 5 AS DEST_COMPONENT_TYPE -- 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 -> QUEUE IN CASE OF XSTREAMIN -- XSTREAM INBOUND SERVER IS PRESENTED AS PROPAGATION RECEIVER
AND WE NEED -- TO ADD A LINK
FROM PROPAGATION RECEIVER TO QUEUE SELECT ('"'||XS.CAP_SRC_DATABASE||'"=>"'|| XS.QUEUE_OWNER||'"."'||XS.QUEUE_NAME||'"') AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 3 AS SOURCE_COMPONENT_TYPE
, ('"'||XS.QUEUE_OWNER||'"."'||XS.QUEUE_NAME||'"') AS DEST_COMPONENT_NAME
, GLOBAL_NAME AS DEST_COMPONENT_DB
, 5 AS DEST_COMPONENT_TYPE
FROM XSTREAM$_SERVER XS
, GLOBAL_NAME
WHERE BITAND(XS.FLAGS
, 2) = 2
AND SUBSTR(XS.SERVER_NAME
, 1
, 4) != 'OGG$' UNION -- REPLICAT -> QUEUE SELECT LTRIM(APPLY_NAME
, 'OGG$') AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 7 AS SOURCE_COMPONENT_TYPE
, ('"'||QUEUE_OWNER||'"."'||QUEUE_NAME||'"') AS DEST_COMPONENT_NAME
, GLOBAL_NAME AS DEST_COMPONENT_DB
, 5 AS DEST_COMPONENT_TYPE
FROM DBA_APPLY
, GLOBAL_NAME
WHERE PURPOSE = 'GOLDENGATE APPLY' UNION -- QUEUE -> APPLY/EXTRACT SELECT ('"'||A.QUEUE_OWNER||'"."'||A.QUEUE_NAME||'"') AS SOURCE_COMPONENT_NAME
, GLOBAL_NAME AS SOURCE_COMPONENT_DB
, 5 AS SOURCE_COMPONENT_TYPE
, DECODE(A.PURPOSE
, 'GOLDENGATE CAPTURE'
, LTRIM(A.APPLY_NAME
, 'OGG$')
, A.APPLY_NAME) AS DEST_COMPONENT_NAME
, GLOBAL_NAME AS DEST_COMPONENT_DB
, DECODE(A.PURPOSE
, 'GOLDENGATE CAPTURE'
, 6
, 4) AS DEST_COMPONENT_TYPE
FROM DBA_APPLY A
, GLOBAL_NAME ) V