SELECT
  x.inst_id,                               -- INST_ID NUMBER
  x.sid_knst,                              -- SID NUMBER
  x.serial_knst,                           -- SERIAL# NUMBER
  x.capnum_knstcap,                        -- CAPTURE# NUMBER
  x.capname_knstcap,                       -- CAPTURE_NAME VARCHAR2(30)
  x.logminer_id_knstcap,                   -- LOGMINER_ID NUMBER
  x.startup_time_knstcap,                  -- STARTUP_TIME DATE
  case                                     -- STATE VARCHAR2(551) 
     when (x.state_knstcap = 10 and d.loaded = 'ACTIVE') 
     then d.current_state || ' ' || d.progress 
     when (x.state_knstcap = 18) 
     then 'WAIT FOR ' || x.subscriber_num || ' SUBSCRIBER(S) INITIALIZING' 
     else DECODE(x.state_knstcap, 0, 'INITIALIZING', 1,'CAPTURING CHANGES', 2, 'EVALUATING RULE', 3,'ENQUEUING MESSAGE', 4, 'SHUTTING DOWN', 5,'ABORTING', 6, 'CREATING LCR', 7, DECODE(x.missing_logfile_info_knstcap, NULL, 'WAITING FOR DICTIONARY REDO', 'WAITING FOR DICTIONARY REDO: ' || x.missing_logfile_info_knstcap), 8, DECODE(x.missing_logfile_info_knstcap, NULL, 'WAITING FOR REDO', 'WAITING FOR REDO: ' || x.missing_logfile_info_knstcap), 9,'PAUSED FOR FLOW CONTROL', 10, 'DICTIONARY INITIALIZATION', 11, 'WAITING FOR APPLY TO BE ENABLED', 12, 'CONNECTING TO APPLY DATABASE', 13, 'WAITING FOR PROPAGATION TO BE ENABLED', 15, 'WAITING FOR A SUBSCRIBER TO BE ADDED', 16, 'WAITING FOR BUFFERED QUEUE TO SHRINK', 17, 'SUSPENDED FOR AUTO SPLIT/MERGE') end,
  m.msgs_filtered,                         -- TOTAL_PREFILTER_DISCARDED NUMBER
  m.msgs_kept,                             -- TOTAL_PREFILTER_KEPT NUMBER
  m.msgs_total,                            -- TOTAL_PREFILTER_EVALUATIONS NUMBER
  x.total_captured_knstcap,                -- TOTAL_MESSAGES_CAPTURED NUMBER
  x.recent_time_knstcap,                   -- CAPTURE_TIME DATE
  x.recent_msg_num_knstcap,                -- CAPTURE_MESSAGE_NUMBER NUMBER
  x.recent_msg_time_knstcap,               -- CAPTURE_MESSAGE_CREATE_TIME DATE
  x.total_messages_created_knstcap,        -- TOTAL_MESSAGES_CREATED NUMBER
  x.total_full_evaluations_knstcap,        -- TOTAL_FULL_EVALUATIONS NUMBER
  x.total_msg_enq_knstcap,                 -- TOTAL_MESSAGES_ENQUEUED NUMBER
  x.enqueue_time_knstcap,                  -- ENQUEUE_TIME DATE
  x.enqueue_msg_num_knstcap,               -- ENQUEUE_MESSAGE_NUMBER NUMBER
  x.enqueue_msg_time_knstcap,              -- ENQUEUE_MESSAGE_CREATE_TIME DATE
  DECODE(                                  -- AVAILABLE_MESSAGE_NUMBER NUMBER
    bitand(x.flags_knstcap, 1), 0, upstream.next_scn, 1, DECODE(bitand(x.flags_knstcap, 8), 0, downstream.next_scn, downstream_real.next_scn)),
  DECODE(                                  -- AVAILABLE_MESSAGE_CREATE_TIME DATE
    bitand(x.flags_knstcap, 1), 0, upstream.next_time, 1, DECODE(bitand(x.flags_knstcap, 8), 0, downstream.next_time, downstream_real.next_time)),
  x.elapsed_capture_time_knstcap,          -- ELAPSED_CAPTURE_TIME NUMBER
  x.elapsed_rule_time_knstcap,             -- ELAPSED_RULE_TIME NUMBER
  x.elapsed_enqueue_time_knstcap,          -- ELAPSED_ENQUEUE_TIME NUMBER
  x.elapsed_lcr_time_knstcap,              -- ELAPSED_LCR_TIME NUMBER
  x.elapsed_wait_time_knstcap,             -- ELAPSED_REDO_WAIT_TIME NUMBER
  x.elapsed_pause_time_knstcap,            -- ELAPSED_PAUSE_TIME NUMBER
  x.state_changed_time_knstcap,            -- STATE_CHANGED_TIME DATE
  x.appname_knstcap,                       -- APPLY_NAME VARCHAR2(30)
  x.apply_dblink_knstcap,                  -- APPLY_DBLINK VARCHAR2(128)
  x.apply_messages_sent_knstcap,           -- APPLY_MESSAGES_SENT NUMBER
  (                                        -- APPLY_BYTES_SENT NUMBER
  SELECT
    sesstat.ksusestv
  FROM
    x$ksusd stat,
    x$ksusesta sesstat,
    x$ksuse sess
  WHERE
    x.sid_knst=sesstat.ksusenum AND x.sid_knst=sess.indx AND x.serial_knst=sess.ksuseser AND bitand(sess.ksspaflg,1)!=0 AND bitand(sess.ksuseflg,1)!=0 AND sesstat.ksusestn = stat.indx AND bitand(sesstat.ksspaflg,1)!=0 AND bitand(sesstat.ksuseflg,1)!=0 AND sesstat.ksusestn<(
    select
      ksusgstl
    from
      x$ksusgif) AND stat.ksusdnam = 'bytes sent via SQL*Net to dblink'), 
  DECODE(                                  -- OPTIMIZATION NUMBER
    BITAND(x.flags_knstcap,6), 2, 1, 4, 2, 0)
FROM
  x$knstcap x,
  (
  SELECT
    session_id,
    sum(skipped_filter_calls) msgs_filtered,
    sum(kept_filter_calls) msgs_kept,
    sum(total_filter_calls) msgs_total
  FROM
    x$logmnr_process
  GROUP BY
    session_id) m, (
  SELECT
    last_write_scn next_scn,
    last_write_scn_time next_time
  FROM
    x$kcrfws ) upstream, (
  SELECT
    session#,
    max(next_change#) next_scn,
    max(next_time) next_time
  FROM
    system.logmnr_log$
  GROUP BY
    session#) downstream, (
  SELECT
    max(to_number(slnxs)) next_scn,
    max(to_date(slnxt, 'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')) next_time
  FROM
    x$kccsl
  WHERE
    sign(slseq)=1) downstream_real, x$logmnr_dictionary_load d
WHERE
  type_knst=8 AND x.logminer_id_knstcap = m.session_id(+) AND x.logminer_id_knstcap = d.session_id(+) AND x.logminer_id_knstcap = downstream.session#(+) AND EXISTS (
  SELECT
    1
  FROM
    v$session s
  WHERE
    s.sid=x.sid_knst AND s.serial#=x.serial_knst)