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_EVENT

Source

View Text - Preformatted


SELECT C.COMPONENT_NAME,
       global_name AS COMPONENT_DB,
       C.COMPONENT_TYPE,
       C.SUB_COMPONENT_TYPE,
       sysdate AS STAT_TIME,
       C.SESSION_ID,
       C.SESSION_SERIAL#,
       V.EVENT,
       0 AS EVENT_COUNT,
       0 AS TOTAL_COUNT,
       SUBSTRB(V.MODULE_NAME,1,
             (SELECT KSUMODLEN FROM X$MODACT_LENGTH)) MODULE_NAME,
       SUBSTRB(V.ACTION_NAME,1,
             (SELECT KSUACTLEN FROM X$MODACT_LENGTH)) ACTION_NAME,
       0, 0, STATE, to_date(NULL, '')
  FROM global_name,
   ( -- CAPTURE
     SELECT capture_name AS COMPONENT_NAME,
          1            AS COMPONENT_TYPE,
          14           AS sub_component_type,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM sys."_GV$SXGG_CAPTURE"
   UNION
   SELECT capture_name AS COMPONENT_NAME,
          1            AS COMPONENT_TYPE,
          decode(l.role,
            'reader',  11,
            'preparer',12,
            'builder', 13,
            14)        AS sub_component_type,
          l.sid        AS SESSION_ID,
          l.serial#    AS SESSION_SERIAL#,
          NULL         AS STATE
   FROM sys."_GV$SXGG_CAPTURE" c, gv$logmnr_process l
   WHERE c.logminer_id = l.session_id
     -- Do not want row for capture process since state is NULL
     AND l.role in ('reader', 'preparer', 'builder')
   UNION
   -- EXTRACT
   SELECT extract_name AS COMPONENT_NAME,
          6            AS COMPONENT_TYPE,
          NULL         AS SUB_COMPONENT_TYPE,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        as STATE
   FROM gv$goldengate_capture
   UNION
   -- REPLICAT
   SELECT LTRIM(apply_name, 'OGG$') AS COMPONENT_NAME,
          7            AS COMPONENT_TYPE,
          NULL         AS SUB_COMPONENT_TYPE,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM gv$gg_apply_receiver
   UNION
    -- APPLY_SERVER, non-XStreamOut case, including OGG
   SELECT apply_name   AS COMPONENT_NAME,
          4            AS COMPONENT_TYPE,
          44           AS SUB_COMPONENT_TYPE,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM sys."_GV$SXGG_APPLY_SERVER"
   WHERE apply_name NOT IN
         (SELECT apply_name FROM dba_apply WHERE UPPER(purpose)= 'XSTREAM OUT')
   UNION
   -- APPLY SERVER, XStreamOut case
   -- In case of XStreamOut, only includes the XStream Outbound Server
   SELECT sas.apply_name   AS COMPONENT_NAME,
          4                AS COMPONENT_TYPE,
          44               AS SUB_COMPONENT_TYPE,
          sas.sid          AS SESSION_ID,
          sas.serial#      AS SESSION_SERIAL#,
          sas.state        AS STATE
   FROM sys."_GV$SXGG_APPLY_SERVER" sas, dba_apply da
   WHERE sas.server_id = 2 AND
         sas.apply_name = da.apply_name AND
         UPPER(da.purpose) = 'XSTREAM OUT'
   UNION
   -- APPLY COORDINATOR
   SELECT apply_name   AS COMPONENT_NAME,
          4            AS COMPONENT_TYPE,
          43           AS SUB_COMPONENT_TYPE,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM sys."_GV$SXGG_APPLY_COORDINATOR"
   UNION
   -- APPLY READER
   SELECT apply_name   AS COMPONENT_NAME,
          4                AS COMPONENT_TYPE,
          42               AS SUB_COMPONENT_TYPE,
          sid          AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM sys."_GV$SXGG_APPLY_READER"
   UNION
   -- PROPAGATION SENDER+RECEIVER
   -- In case of XStreamIn, we will populate the XStream inbound server as
   -- PROPAGATION RECEIVER, so do not show it as PROPAGATION SENDER+RECEIVER
   SELECT apply_name   AS COMPONENT_NAME,
          4            AS COMPONENT_TYPE,
          41           AS SUB_COMPONENT_TYPE,
          proxy_sid    AS SESSION_ID,
          proxy_serial AS SESSION_SERIAL#,
          state        AS STATE
   FROM sys."_GV$SXGG_APPLY_READER"
   WHERE proxy_sid > 0 AND
         ((proxy_sid, proxy_serial) NOT IN
          (SELECT sid, serial#
           FROM sys."_GV$SXGG_CAPTURE"))
    AND  (apply_name NOT IN (SELECT apply_name
                             FROM dba_apply WHERE UPPER(purpose) in
                             ('XSTREAM IN', 'GOLDENGATE APPLY')))
   UNION
   -- PROPAGATION SENDER
   SELECT ('"'||queue_schema||'"."'||queue_name||'"=>'||
           CASE WHEN dblink IS NOT NULL AND
                     (dst_queue_schema IS NULL OR dst_queue_name IS NULL)
                THEN dblink
                ELSE ('"'||dst_queue_schema||'"."'||dst_queue_name||
                      '"@'||dst_database_name)
           END)        AS COMPONENT_NAME,
          2            AS COMPONENT_TYPE,
          NULL         AS SUB_COMPONENT_TYPE,
          session_id   AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM gv$propagation_sender
   UNION
   -- PROPAGATION RECEIVER, exclude the case for XStreamIn where src_queue_schema
   -- and src_queue_name are NULL. Also exclude local anr for backward
   -- compatibility. A propagation receiver is considered local anr if source
   -- and destination queues are the same and the source db is the same as
   -- the local db.
   SELECT ('"'||src_queue_schema||'"."'||src_queue_name||
           '"@'||src_dbname||'=>"'||
           dst_queue_schema||'"."'||dst_queue_name||'"')
                       AS COMPONENT_NAME,
          3            AS COMPONENT_TYPE,
          NULL         AS SUB_COMPONENT_TYPE,
          session_id   AS SESSION_ID,
          serial#      AS SESSION_SERIAL#,
          state        AS STATE
   FROM gv$propagation_receiver P
   WHERE src_queue_schema IS NOT NULL AND
         src_queue_name IS NOT NULL AND
         NOT ((P.SRC_QUEUE_SCHEMA = P.DST_QUEUE_SCHEMA) and
              (P.SRC_QUEUE_NAME = P.DST_QUEUE_NAME) and
              (P.SRC_DBNAME = (SELECT GLOBAL_NAME FROM GLOBAL_NAME)))
   UNION
   -- PROPAGATION RECEIVER in case of XStreamIn only (no Replicat), we will
   --  populate the XStream inbound server as PROPAGATION RECEIVER
   -- Note: in gv$propagation receiver, there is no source queue name and
   -- queue owner, the src_dbname is populated with the XStreamIn source
   -- name, thus the src_dbname in gv$propagation receiver should be the same
   -- as the cap_src_database in xstream$_server table.
   SELECT ('"'||pr.src_dbname||'"=>"'||
           pr.dst_queue_schema||'"."'||pr.dst_queue_name||'"')
                       AS COMPONENT_NAME,
          3            AS COMPONENT_TYPE,
          NULL         AS SUB_COMPONENT_TYPE,
          pr.session_id   AS SESSION_ID,
          pr.serial#      AS SESSION_SERIAL#,
          pr.state        AS STATE
   FROM gv$propagation_receiver pr, xstream$_server xs
   WHERE  pr.src_dbname = xs.cap_src_database AND
          NOT ((pr.SRC_QUEUE_SCHEMA = pr.DST_QUEUE_SCHEMA) and
               (pr.SRC_QUEUE_NAME = pr.DST_QUEUE_NAME) and
               (pr.SRC_DBNAME = (SELECT GLOBAL_NAME FROM GLOBAL_NAME))) AND
          SUBSTR(xs.server_name, 1, 4) != 'OGG$'
   ) C,
   -- Need to get proper size for EVENT, MODULE_NAME, ACTION_NAME
 ( SELECT NULL               AS COMPONENT_NAME,
          LPAD(' ', 64, ' ') AS EVENT,
          LPAD(' ', 64, ' ') AS MODULE_NAME,
          LPAD(' ', 64, ' ') AS ACTION_NAME
   FROM DUAL) V
WHERE C.SESSION_ID IS NOT NULL AND
      C.SESSION_SERIAL# IS NOT NULL AND
      C.COMPONENT_NAME = V.COMPONENT_NAME (+)

View Text - HTML Formatted

SELECT C.COMPONENT_NAME
, GLOBAL_NAME AS COMPONENT_DB
, C.COMPONENT_TYPE
, C.SUB_COMPONENT_TYPE
, SYSDATE AS STAT_TIME
, C.SESSION_ID
, C.SESSION_SERIAL#
, V.EVENT
, 0 AS EVENT_COUNT
, 0 AS TOTAL_COUNT
, SUBSTRB(V.MODULE_NAME
, 1
, (SELECT KSUMODLEN
FROM X$MODACT_LENGTH)) MODULE_NAME
, SUBSTRB(V.ACTION_NAME
, 1
, (SELECT KSUACTLEN
FROM X$MODACT_LENGTH)) ACTION_NAME
, 0
, 0
, STATE
, TO_DATE(NULL
, '')
FROM GLOBAL_NAME
, ( -- CAPTURE SELECT CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, 14 AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM SYS."_GV$SXGG_CAPTURE" UNION SELECT CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, DECODE(L.ROLE
, 'READER'
, 11
, 'PREPARER'
, 12
, 'BUILDER'
, 13
, 14) AS SUB_COMPONENT_TYPE
, L.SID AS SESSION_ID
, L.SERIAL# AS SESSION_SERIAL#
, NULL AS STATE
FROM SYS."_GV$SXGG_CAPTURE" C
, GV$LOGMNR_PROCESS L
WHERE C.LOGMINER_ID = L.SESSION_ID -- DO NOT WANT ROW FOR CAPTURE PROCESS SINCE STATE IS NULL
AND L.ROLE IN ('READER'
, 'PREPARER'
, 'BUILDER') UNION -- EXTRACT SELECT EXTRACT_NAME AS COMPONENT_NAME
, 6 AS COMPONENT_TYPE
, NULL AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM GV$GOLDENGATE_CAPTURE UNION -- REPLICAT SELECT LTRIM(APPLY_NAME
, 'OGG$') AS COMPONENT_NAME
, 7 AS COMPONENT_TYPE
, NULL AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM GV$GG_APPLY_RECEIVER UNION -- APPLY_SERVER
, NON-XSTREAMOUT CASE
, INCLUDING OGG SELECT APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 44 AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM SYS."_GV$SXGG_APPLY_SERVER"
WHERE APPLY_NAME NOT IN (SELECT APPLY_NAME
FROM DBA_APPLY
WHERE UPPER(PURPOSE)= 'XSTREAM OUT') UNION -- APPLY SERVER
, XSTREAMOUT CASE -- IN CASE OF XSTREAMOUT
, ONLY INCLUDES THE XSTREAM OUTBOUND SERVER SELECT SAS.APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 44 AS SUB_COMPONENT_TYPE
, SAS.SID AS SESSION_ID
, SAS.SERIAL# AS SESSION_SERIAL#
, SAS.STATE AS STATE
FROM SYS."_GV$SXGG_APPLY_SERVER" SAS
, DBA_APPLY DA
WHERE SAS.SERVER_ID = 2 AND SAS.APPLY_NAME = DA.APPLY_NAME AND UPPER(DA.PURPOSE) = 'XSTREAM OUT' UNION -- APPLY COORDINATOR SELECT APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 43 AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM SYS."_GV$SXGG_APPLY_COORDINATOR" UNION -- APPLY READER SELECT APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 42 AS SUB_COMPONENT_TYPE
, SID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM SYS."_GV$SXGG_APPLY_READER" UNION -- PROPAGATION SENDER+RECEIVER -- IN CASE OF XSTREAMIN
, WE WILL POPULATE THE XSTREAM INBOUND SERVER AS -- PROPAGATION RECEIVER
, SO DO NOT SHOW IT AS PROPAGATION SENDER+RECEIVER SELECT APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, 41 AS SUB_COMPONENT_TYPE
, PROXY_SID AS SESSION_ID
, PROXY_SERIAL AS SESSION_SERIAL#
, STATE AS STATE
FROM SYS."_GV$SXGG_APPLY_READER"
WHERE PROXY_SID > 0 AND ((PROXY_SID
, PROXY_SERIAL) NOT IN (SELECT SID
, SERIAL#
FROM SYS."_GV$SXGG_CAPTURE"))
AND (APPLY_NAME NOT IN (SELECT APPLY_NAME
FROM DBA_APPLY
WHERE UPPER(PURPOSE) IN ('XSTREAM IN'
, 'GOLDENGATE APPLY'))) UNION -- PROPAGATION SENDER SELECT ('"'||QUEUE_SCHEMA||'"."'||QUEUE_NAME||'"=>'|| CASE WHEN DBLINK IS NOT NULL AND (DST_QUEUE_SCHEMA IS NULL OR DST_QUEUE_NAME IS NULL) THEN DBLINK ELSE ('"'||DST_QUEUE_SCHEMA||'"."'||DST_QUEUE_NAME|| '"@'||DST_DATABASE_NAME) END) AS COMPONENT_NAME
, 2 AS COMPONENT_TYPE
, NULL AS SUB_COMPONENT_TYPE
, SESSION_ID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM GV$PROPAGATION_SENDER UNION -- PROPAGATION RECEIVER
, EXCLUDE THE CASE FOR XSTREAMIN
WHERE SRC_QUEUE_SCHEMA --
AND SRC_QUEUE_NAME ARE NULL. ALSO EXCLUDE LOCAL ANR FOR BACKWARD -- COMPATIBILITY. A PROPAGATION RECEIVER IS CONSIDERED LOCAL ANR IF SOURCE --
AND DESTINATION QUEUES ARE THE SAME
AND THE SOURCE DB IS THE SAME AS -- THE LOCAL DB. SELECT ('"'||SRC_QUEUE_SCHEMA||'"."'||SRC_QUEUE_NAME|| '"@'||SRC_DBNAME||'=>"'|| DST_QUEUE_SCHEMA||'"."'||DST_QUEUE_NAME||'"') AS COMPONENT_NAME
, 3 AS COMPONENT_TYPE
, NULL AS SUB_COMPONENT_TYPE
, SESSION_ID AS SESSION_ID
, SERIAL# AS SESSION_SERIAL#
, STATE AS STATE
FROM GV$PROPAGATION_RECEIVER P
WHERE SRC_QUEUE_SCHEMA IS NOT NULL AND SRC_QUEUE_NAME IS NOT NULL AND NOT ((P.SRC_QUEUE_SCHEMA = P.DST_QUEUE_SCHEMA) AND (P.SRC_QUEUE_NAME = P.DST_QUEUE_NAME) AND (P.SRC_DBNAME = (SELECT GLOBAL_NAME
FROM GLOBAL_NAME))) UNION -- PROPAGATION RECEIVER IN CASE OF XSTREAMIN ONLY (NO REPLICAT)
, WE WILL -- POPULATE THE XSTREAM INBOUND SERVER AS PROPAGATION RECEIVER -- NOTE: IN GV$PROPAGATION RECEIVER
, THERE IS NO SOURCE QUEUE NAME AND -- QUEUE OWNER
, THE SRC_DBNAME IS POPULATED WITH THE XSTREAMIN SOURCE -- NAME
, THUS THE SRC_DBNAME IN GV$PROPAGATION RECEIVER SHOULD BE THE SAME -- AS THE CAP_SRC_DATABASE IN XSTREAM$_SERVER TABLE. SELECT ('"'||PR.SRC_DBNAME||'"=>"'|| PR.DST_QUEUE_SCHEMA||'"."'||PR.DST_QUEUE_NAME||'"') AS COMPONENT_NAME
, 3 AS COMPONENT_TYPE
, NULL AS SUB_COMPONENT_TYPE
, PR.SESSION_ID AS SESSION_ID
, PR.SERIAL# AS SESSION_SERIAL#
, PR.STATE AS STATE
FROM GV$PROPAGATION_RECEIVER PR
, XSTREAM$_SERVER XS
WHERE PR.SRC_DBNAME = XS.CAP_SRC_DATABASE AND NOT ((PR.SRC_QUEUE_SCHEMA = PR.DST_QUEUE_SCHEMA) AND (PR.SRC_QUEUE_NAME = PR.DST_QUEUE_NAME) AND (PR.SRC_DBNAME = (SELECT GLOBAL_NAME
FROM GLOBAL_NAME))) AND SUBSTR(XS.SERVER_NAME
, 1
, 4) != 'OGG$' ) C
, -- NEED TO GET PROPER SIZE FOR EVENT
, MODULE_NAME
, ACTION_NAME ( SELECT NULL AS COMPONENT_NAME
, LPAD(' '
, 64
, ' ') AS EVENT
, LPAD(' '
, 64
, ' ') AS MODULE_NAME
, LPAD(' '
, 64
, ' ') AS ACTION_NAME
FROM DUAL) V WHERE C.SESSION_ID IS NOT NULL AND C.SESSION_SERIAL# IS NOT NULL AND C.COMPONENT_NAME = V.COMPONENT_NAME (+)