GV$STREAMS_CAPTURE

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(73)
  when (x.state_knstcap = 10 and d.loaded = 'ACTIVE') then d.current_state || ' ' || d.progress 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, 'WAITING FOR DICTIONARY REDO', 8, 'WAITING FOR REDO', 9, 'PAUSED FOR FLOW CONTROL', 10, 'DICTIONARY INITIALIZATION') 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, downstream.next_scn),
DECODE                   -- AVAILABLE_MESSAGE_CREATE_TIME DATE
  (bitand (x.flags_knstcap, 1), 0, upstream.next_time, 1, downstream.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
  
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,
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)
home |  up  |  mailbox