VIEW: SYS.ALL_CHANGE_SETS

Source

View Text - Preformatted

SELECT
   s.set_name, s.change_source_name, s.begin_date, s.end_date, s.begin_scn,
   s.end_scn, s.freshness_date, s.freshness_scn,
   (case when (s.change_source_name = 'SYNC_SOURCE') then s.advance_enabled
         when (a.status = 2 and c.status = 2) then 'Y'
         when a.status = 2 then 'C'
         else 'N' end) advance_enabled,
   s.ignore_ddl, s.created, s.rollback_segment_name, s.advancing, s.purging,
   s.lowest_scn, s.tablespace,
   (case when (s.change_source_name = 'SYNC_SOURCE') then s.capture_enabled
         else decode(a.status, 2, 'Y', 'N') end) capture_enabled,
   s.stop_on_ddl,
   (case when (s.change_source_name = 'SYNC_SOURCE') then s.capture_error
         when (a.status = 1 and e.error_number is not null) then 'Y'
         when (a.apply_name is null) then 'Y'
         else 'N' end) capture_error,
   s.capture_name, s.queue_name, s.queue_table_name,
   s.apply_name, s.set_description, s.publisher, s.lowest_timestamp,
   s.time_scn_name
  FROM sys.cdc_change_sets$ s,
       sys.streams$_apply_process a, sys.streams$_capture_process c,
       sys.apply$_error e
  WHERE s.apply_name = a.apply_name (+)
  AND a.apply# = e.apply# (+)
  AND s.capture_name = c.capture_name (+)

View Text - HTML Formatted

SELECT S.SET_NAME
, S.CHANGE_SOURCE_NAME
, S.BEGIN_DATE
, S.END_DATE
, S.BEGIN_SCN
, S.END_SCN
, S.FRESHNESS_DATE
, S.FRESHNESS_SCN
, (CASE WHEN (S.CHANGE_SOURCE_NAME = 'SYNC_SOURCE') THEN S.ADVANCE_ENABLED WHEN (A.STATUS = 2
AND C.STATUS = 2) THEN 'Y' WHEN A.STATUS = 2 THEN 'C' ELSE 'N' END) ADVANCE_ENABLED
, S.IGNORE_DDL
, S.CREATED
, S.ROLLBACK_SEGMENT_NAME
, S.ADVANCING
, S.PURGING
, S.LOWEST_SCN
, S.TABLESPACE
, (CASE WHEN (S.CHANGE_SOURCE_NAME = 'SYNC_SOURCE') THEN S.CAPTURE_ENABLED ELSE DECODE(A.STATUS
, 2
, 'Y'
, 'N') END) CAPTURE_ENABLED
, S.STOP_ON_DDL
, (CASE WHEN (S.CHANGE_SOURCE_NAME = 'SYNC_SOURCE') THEN S.CAPTURE_ERROR WHEN (A.STATUS = 1
AND E.ERROR_NUMBER IS NOT NULL) THEN 'Y' WHEN (A.APPLY_NAME IS NULL) THEN 'Y' ELSE 'N' END) CAPTURE_ERROR
, S.CAPTURE_NAME
, S.QUEUE_NAME
, S.QUEUE_TABLE_NAME
, S.APPLY_NAME
, S.SET_DESCRIPTION
, S.PUBLISHER
, S.LOWEST_TIMESTAMP
, S.TIME_SCN_NAME
FROM SYS.CDC_CHANGE_SETS$ S
, SYS.STREAMS$_APPLY_PROCESS A
, SYS.STREAMS$_CAPTURE_PROCESS C
, SYS.APPLY$_ERROR E
WHERE S.APPLY_NAME = A.APPLY_NAME (+)
AND A.APPLY# = E.APPLY# (+)
AND S.CAPTURE_NAME = C.CAPTURE_NAME (+)