VIEW: SYS._DBA_STREAMS_COMPONENT_PROP

Source

View Text - Preformatted

SELECT  v.COMPONENT_NAME,
        global_name                   as COMPONENT_DB,
        v.COMPONENT_TYPE,
        v.PROP_NAME,
        v.PROP_VALUE,
        0, 0, NULL, to_date(NULL, '')
FROM ( -- Capture property: SOURCE_DATABASE
       SELECT c.capture_name          as COMPONENT_NAME,
              1                       as COMPONENT_TYPE,
              'SOURCE_DATABASE'       as PROP_NAME,
              c.source_dbname         as PROP_VALUE
       -- OPTIMIZE: Replace dba_capture with sys.streams$_capture_process
       FROM sys.streams$_capture_process c
       UNION
       -- CAPTURE property: PARALLELISM
       SELECT c.capture_name          as COMPONENT_NAME,
              1                       as COMPONENT_TYPE,
              'PARALLELISM'           as PROP_NAME,
              p.value                 as PROP_VALUE
       FROM sys.streams$_capture_process c,
            sys.streams$_process_params p
       WHERE c.capture# = p.process# AND
             p.name = 'PARALLELISM' AND
             p.process_type = 2       -- type 2 indicates capture process
       UNION
       -- Capture property: OPTIMIZATION_MODE
       SELECT c.capture_name          as COMPONENT_NAME,
              1                       as COMPONENT_TYPE,
              'OPTIMIZATION_MODE'     as PROP_NAME,
              decode(c.optimization, 1, '1', 2, '2', '0')
                                      as PROP_VALUE
       FROM SYS."_GV$SXGG_CAPTURE" c
       UNION
       -- Apply - property: PARALLELISM
       SELECT apply_name                as COMPONENT_NAME,
              4                         as COMPONENT_TYPE,
              'PARALLELISM'             as PROP_NAME,
              to_char(count(server_id)) as PROP_VALUE
       FROM  SYS."_GV$SXGG_APPLY_SERVER"
       GROUP BY apply_name
       UNION
      -- Replicat - property: PARALLELISM
       SELECT ltrim(apply_name, 'OGG$') as COMPONENT_NAME,
              7                         as COMPONENT_TYPE,
              'PARALLELISM'             as PROP_NAME,
              to_char(count(server_id)) as PROP_VALUE
       FROM  gv$gg_apply_server
       GROUP BY apply_name
       UNION
       -- Extract property: PARALLELISM - always 1
       SELECT e.extract_name              as COMPONENT_NAME,
              6                         as COMPONENT_TYPE,
              'PARALLELISM'             as PROP_NAME,
              '1'                         as PROP_VALUE
       FROM gv$goldengate_capture e
       UNION
       -- Apply/Extract property: SOURCE_DATABASE
       SELECT decode(da.purpose, 'GoldenGate Capture',
                     ltrim(ap.apply_name, 'OGG$'),
                     ap.apply_name)   as COMPONENT_NAME,
              decode(da.purpose, 'GoldenGate Capture', 6, 4)
                                      as COMPONENT_TYPE,
              'SOURCE_DATABASE'       as PROP_NAME,
              am.source_db_name       as PROP_VALUE
       -- OPTIMIZE: Replace dba_apply_progress with
       -- sys.streams$_apply_process and sys.streams$_apply_milestone
       FROM sys.streams$_apply_process ap,
            dba_apply da,
            sys.streams$_apply_milestone am
       WHERE ap.apply# = am.apply# (+) AND
             da.apply_name = ap.apply_name
       UNION
       -- Apply/Extract property: APPLY_CAPTURED
       SELECT decode(da.purpose,
                     'GoldenGate Capture', ltrim(a.apply_name, 'OGG$'),
                     a.apply_name)   as COMPONENT_NAME,
              decode(da.purpose, 'GoldenGate Capture', 6, 4)
                                      as COMPONENT_TYPE,
              'APPLY_CAPTURED'        as PROP_NAME,
              decode(bitand(a.flags, 1), 1, 'YES', 0, 'NO')
                                      as PROP_VALUE
       FROM sys.streams$_apply_process a,
            dba_apply da
       WHERE a.apply_name = da.apply_name
       UNION
       -- Apply/Extract property: MESSAGE_DELIVERY_MODE
       SELECT decode(da.purpose,
                     'GoldenGate Capture', ltrim(a.apply_name, 'OGG$'),
                     a.apply_name)    as COMPONENT_NAME,
              decode(da.purpose, 'GoldenGate Capture', 6, 4)
                                      as COMPONENT_TYPE,
              'MESSAGE_DELIVERY_MODE' as PROP_NAME,
              decode(bitand(a.flags, 1), 1, 'CAPTURED',
                     decode(bitand(a.flags, 128),
                            128, 'CAPTURED', 0, 'PERSISTENT'))
                                      as PROP_VALUE
       FROM sys.streams$_apply_process a,
            dba_apply da
       WHERE a.apply_name = da.apply_name
     ) v, global_name

View Text - HTML Formatted

SELECT V.COMPONENT_NAME
, GLOBAL_NAME AS COMPONENT_DB
, V.COMPONENT_TYPE
, V.PROP_NAME
, V.PROP_VALUE
, 0
, 0
, NULL
, TO_DATE(NULL
, '') FROM ( -- CAPTURE PROPERTY: SOURCE_DATABASE SELECT C.CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, 'SOURCE_DATABASE' AS PROP_NAME
, C.SOURCE_DBNAME AS PROP_VALUE -- OPTIMIZE: REPLACE DBA_CAPTURE WITH SYS.STREAMS$_CAPTURE_PROCESS
FROM SYS.STREAMS$_CAPTURE_PROCESS C UNION -- CAPTURE PROPERTY: PARALLELISM SELECT C.CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, 'PARALLELISM' AS PROP_NAME
, P.VALUE AS PROP_VALUE
FROM SYS.STREAMS$_CAPTURE_PROCESS C
, SYS.STREAMS$_PROCESS_PARAMS P
WHERE C.CAPTURE# = P.PROCESS# AND P.NAME = 'PARALLELISM' AND P.PROCESS_TYPE = 2 -- TYPE 2 INDICATES CAPTURE PROCESS UNION -- CAPTURE PROPERTY: OPTIMIZATION_MODE SELECT C.CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, 'OPTIMIZATION_MODE' AS PROP_NAME
, DECODE(C.OPTIMIZATION
, 1
, '1'
, 2
, '2'
, '0') AS PROP_VALUE
FROM SYS."_GV$SXGG_CAPTURE" C UNION -- APPLY - PROPERTY: PARALLELISM SELECT APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 'PARALLELISM' AS PROP_NAME
, TO_CHAR(COUNT(SERVER_ID)) AS PROP_VALUE
FROM SYS."_GV$SXGG_APPLY_SERVER" GROUP BY APPLY_NAME UNION -- REPLICAT - PROPERTY: PARALLELISM SELECT LTRIM(APPLY_NAME
, 'OGG$') AS COMPONENT_NAME
, 7 AS COMPONENT_TYPE
, 'PARALLELISM' AS PROP_NAME
, TO_CHAR(COUNT(SERVER_ID)) AS PROP_VALUE
FROM GV$GG_APPLY_SERVER GROUP BY APPLY_NAME UNION -- EXTRACT PROPERTY: PARALLELISM - ALWAYS 1 SELECT E.EXTRACT_NAME AS COMPONENT_NAME
, 6 AS COMPONENT_TYPE
, 'PARALLELISM' AS PROP_NAME
, '1' AS PROP_VALUE
FROM GV$GOLDENGATE_CAPTURE E UNION -- APPLY/EXTRACT PROPERTY: SOURCE_DATABASE SELECT DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, LTRIM(AP.APPLY_NAME
, 'OGG$')
, AP.APPLY_NAME) AS COMPONENT_NAME
, DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, 6
, 4) AS COMPONENT_TYPE
, 'SOURCE_DATABASE' AS PROP_NAME
, AM.SOURCE_DB_NAME AS PROP_VALUE -- OPTIMIZE: REPLACE DBA_APPLY_PROGRESS WITH -- SYS.STREAMS$_APPLY_PROCESS
AND SYS.STREAMS$_APPLY_MILESTONE
FROM SYS.STREAMS$_APPLY_PROCESS AP
, DBA_APPLY DA
, SYS.STREAMS$_APPLY_MILESTONE AM
WHERE AP.APPLY# = AM.APPLY# (+) AND DA.APPLY_NAME = AP.APPLY_NAME UNION -- APPLY/EXTRACT PROPERTY: APPLY_CAPTURED SELECT DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, LTRIM(A.APPLY_NAME
, 'OGG$')
, A.APPLY_NAME) AS COMPONENT_NAME
, DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, 6
, 4) AS COMPONENT_TYPE
, 'APPLY_CAPTURED' AS PROP_NAME
, DECODE(BITAND(A.FLAGS
, 1)
, 1
, 'YES'
, 0
, 'NO') AS PROP_VALUE
FROM SYS.STREAMS$_APPLY_PROCESS A
, DBA_APPLY DA
WHERE A.APPLY_NAME = DA.APPLY_NAME UNION -- APPLY/EXTRACT PROPERTY: MESSAGE_DELIVERY_MODE SELECT DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, LTRIM(A.APPLY_NAME
, 'OGG$')
, A.APPLY_NAME) AS COMPONENT_NAME
, DECODE(DA.PURPOSE
, 'GOLDENGATE CAPTURE'
, 6
, 4) AS COMPONENT_TYPE
, 'MESSAGE_DELIVERY_MODE' AS PROP_NAME
, DECODE(BITAND(A.FLAGS
, 1)
, 1
, 'CAPTURED'
, DECODE(BITAND(A.FLAGS
, 128)
, 128
, 'CAPTURED'
, 0
, 'PERSISTENT')) AS PROP_VALUE
FROM SYS.STREAMS$_APPLY_PROCESS A
, DBA_APPLY DA
WHERE A.APPLY_NAME = DA.APPLY_NAME ) V
, GLOBAL_NAME