VIEW: SYS.DBA_CAPTURE

Source

View Text - Preformatted

select capture_name, queue_name, queue_owner, rule_set_name,
   rule_set_owner, capture_user, start_scn, status, captured_scn, applied_scn,
   use_database_link, first_scn, source_database, source_dbid,
   source_resetlogs_scn, source_resetlogs_time, logminer_id,
   negative_rule_set_name, negative_rule_set_owner, max_checkpoint_scn,
   required_checkpoint_scn, logfile_assignment, status_change_time,
   error_number, error_message, version, capture_type, last_enqueued_scn,
   checkpoint_retention_time, start_time, purpose, source_root_name,
   null, null, null, null
from "_SXGG_DBA_CAPTURE" cp
where cp.purpose NOT IN ('GoldenGate Capture', 'XStream Out') -- Streams Capture
union
select capture_name, queue_name, queue_owner, rule_set_name,
   rule_set_owner, capture_user, start_scn, status, captured_scn, applied_scn,
   use_database_link, first_scn, source_database, source_dbid,
   source_resetlogs_scn, source_resetlogs_time, logminer_id,
   negative_rule_set_name, negative_rule_set_owner, max_checkpoint_scn,
   required_checkpoint_scn, logfile_assignment, status_change_time,
   error_number, error_message, version, capture_type, last_enqueued_scn,
   checkpoint_retention_time, start_time, purpose, source_root_name,
   decode((select count(*) from xstream$_server x
           where x.capture_name = cp.capture_name),
           1,
           decode(cp.purpose, 'GoldenGate Capture',
                     (select substr(x.user_comment,
                             1,instr(x.user_comment,' ') - 1)   -- Extract Name
                      from xstream$_server x
                      where x.capture_name = cp.capture_name),
                     (select x.server_name              -- Outbound Server Name
                      from xstream$_server x
                      where x.capture_name = cp.capture_name)
                     ),
           NULL),
   decode((select count(*) from xstream$_server x
           where x.capture_name = cp.capture_name),
           1,
           (decode(cp.status,
                   'ENABLED',
                   decode((select bitand(flags,8) from xstream$_server x
                          where x.capture_name = cp.capture_name),
                          8,  -- 0x8 client attached
                          'ATTACHED', 'DETACHED'),
                   'DETACHED')),
            NULL),
   cp.oldest_scn,
   decode((select count(*)
           from sys.xstream$_server x, sys.streams$_apply_process ap,
                sys.streams$_apply_milestone am
           where cp.capture_name = x.capture_name
                 and x.server_name = ap.apply_name
                 and cp.queue_owner = ap.queue_owner
                 and cp.queue_name = ap.queue_name
                 and ap.apply# = am.apply#),
           1,
           (select am.start_scn
            from sys.xstream$_server x, sys.streams$_apply_process ap,
                 sys.streams$_apply_milestone am
            where cp.capture_name = x.capture_name
                  and x.server_name = ap.apply_name
                  and cp.queue_owner = ap.queue_owner
                  and cp.queue_name = ap.queue_name
                  and ap.apply# = am.apply#),
           NULL)
from "_SXGG_DBA_CAPTURE" cp
where cp.purpose in ('GoldenGate Capture', 'XStream Out')

View Text - HTML Formatted

SELECT CAPTURE_NAME
, QUEUE_NAME
, QUEUE_OWNER
, RULE_SET_NAME
, RULE_SET_OWNER
, CAPTURE_USER
, START_SCN
, STATUS
, CAPTURED_SCN
, APPLIED_SCN
, USE_DATABASE_LINK
, FIRST_SCN
, SOURCE_DATABASE
, SOURCE_DBID
, SOURCE_RESETLOGS_SCN
, SOURCE_RESETLOGS_TIME
, LOGMINER_ID
, NEGATIVE_RULE_SET_NAME
, NEGATIVE_RULE_SET_OWNER
, MAX_CHECKPOINT_SCN
, REQUIRED_CHECKPOINT_SCN
, LOGFILE_ASSIGNMENT
, STATUS_CHANGE_TIME
, ERROR_NUMBER
, ERROR_MESSAGE
, VERSION
, CAPTURE_TYPE
, LAST_ENQUEUED_SCN
, CHECKPOINT_RETENTION_TIME
, START_TIME
, PURPOSE
, SOURCE_ROOT_NAME
, NULL
, NULL
, NULL
, NULL FROM "_SXGG_DBA_CAPTURE" CP WHERE CP.PURPOSE NOT IN ('GOLDENGATE CAPTURE'
, 'XSTREAM OUT') -- STREAMS CAPTURE UNION SELECT CAPTURE_NAME
, QUEUE_NAME
, QUEUE_OWNER
, RULE_SET_NAME
, RULE_SET_OWNER
, CAPTURE_USER
, START_SCN
, STATUS
, CAPTURED_SCN
, APPLIED_SCN
, USE_DATABASE_LINK
, FIRST_SCN
, SOURCE_DATABASE
, SOURCE_DBID
, SOURCE_RESETLOGS_SCN
, SOURCE_RESETLOGS_TIME
, LOGMINER_ID
, NEGATIVE_RULE_SET_NAME
, NEGATIVE_RULE_SET_OWNER
, MAX_CHECKPOINT_SCN
, REQUIRED_CHECKPOINT_SCN
, LOGFILE_ASSIGNMENT
, STATUS_CHANGE_TIME
, ERROR_NUMBER
, ERROR_MESSAGE
, VERSION
, CAPTURE_TYPE
, LAST_ENQUEUED_SCN
, CHECKPOINT_RETENTION_TIME
, START_TIME
, PURPOSE
, SOURCE_ROOT_NAME
, DECODE((SELECT COUNT(*)
FROM XSTREAM$_SERVER X
WHERE X.CAPTURE_NAME = CP.CAPTURE_NAME)
, 1
, DECODE(CP.PURPOSE
, 'GOLDENGATE CAPTURE'
, (SELECT SUBSTR(X.USER_COMMENT
, 1
, INSTR(X.USER_COMMENT
, ' ') - 1) -- EXTRACT NAME
FROM XSTREAM$_SERVER X
WHERE X.CAPTURE_NAME = CP.CAPTURE_NAME)
, (SELECT X.SERVER_NAME -- OUTBOUND SERVER NAME
FROM XSTREAM$_SERVER X
WHERE X.CAPTURE_NAME = CP.CAPTURE_NAME) )
, NULL)
, DECODE((SELECT COUNT(*)
FROM XSTREAM$_SERVER X
WHERE X.CAPTURE_NAME = CP.CAPTURE_NAME)
, 1
, (DECODE(CP.STATUS
, 'ENABLED'
, DECODE((SELECT BITAND(FLAGS
, 8)
FROM XSTREAM$_SERVER X
WHERE X.CAPTURE_NAME = CP.CAPTURE_NAME)
, 8
, -- 0X8 CLIENT ATTACHED 'ATTACHED'
, 'DETACHED')
, 'DETACHED'))
, NULL)
, CP.OLDEST_SCN
, DECODE((SELECT COUNT(*)
FROM SYS.XSTREAM$_SERVER X
, SYS.STREAMS$_APPLY_PROCESS AP
, SYS.STREAMS$_APPLY_MILESTONE AM
WHERE CP.CAPTURE_NAME = X.CAPTURE_NAME
AND X.SERVER_NAME = AP.APPLY_NAME
AND CP.QUEUE_OWNER = AP.QUEUE_OWNER
AND CP.QUEUE_NAME = AP.QUEUE_NAME
AND AP.APPLY# = AM.APPLY#)
, 1
, (SELECT AM.START_SCN
FROM SYS.XSTREAM$_SERVER X
, SYS.STREAMS$_APPLY_PROCESS AP
, SYS.STREAMS$_APPLY_MILESTONE AM
WHERE CP.CAPTURE_NAME = X.CAPTURE_NAME
AND X.SERVER_NAME = AP.APPLY_NAME
AND CP.QUEUE_OWNER = AP.QUEUE_OWNER
AND CP.QUEUE_NAME = AP.QUEUE_NAME
AND AP.APPLY# = AM.APPLY#)
, NULL) FROM "_SXGG_DBA_CAPTURE" CP WHERE CP.PURPOSE IN ('GOLDENGATE CAPTURE'
, 'XSTREAM OUT')