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