STREAMS - Capture/Propagation/Apply

----chk_capture.sql
set feedback off
set heading on
set linesize 120
column capture_name format a25
column status format a30
column status_change_time format a15
column capture_time format a15
column last_enqueued_scn format 99999999999999999999
column start_scn format 99999999999999999999
column first_scn format 99999999999999999999
column captured_scn format 99999999999999999999
select c.capture_name
, c.status || ' - ' || DECODE(v.state,'DICTIONARY INITIALIZATION','DICT. INITIALIZE',v.state) || ' n' || inst_id STATUS
, to_char(c.status_change_time,'DD-MON-YY HH24:MI') STATUS_CHANGE_TIME
, c.captured_scn, to_char(v.capture_time,'DD-MON-YY HH24:MI') CAPTURE_TIME
from dba_capture c, gv$streams_capture v
where c.capture_name = v.capture_name (+)
/
set feedback on

----chk_propagation.sql
column propagation_status format a100
select s.DESTINATION || ' State:' || DECODE(s.SCHEDULE_DISABLED,'Y','DISABLED','N','ENABLED ', 'UNKNOWN ') || ' Msgs:' || p.total_msgs || ' Errs:' || FAILURES PROPAGATION_STATUS
FROM dba_queue_schedules s, gv$propagation_sender p
where p.dblink = s.destination
and s.message_delivery_mode='PERSISTENT';

----chk_apply
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A25
COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22
COLUMN PROC_NAME HEADING 'Process|Name' FORMAT A7
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Events|Dequeued' FORMAT 99999999999
COLUMN msg_number FORMAT 9999999999999999
COLUMN dq_time format a15
COLUMN Status format a10
set linesize 120
set feedback off;
set heading on
set pagesize 80
　
SELECT r.APPLY_NAME,
ap.status || ' n' || s.inst_id STATUS,
SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROC_NAME,
r.STATE|| ' ApplySrv: ' || DECODE(srv.state,'EXECUTE TRANSACTION','EXEC TRANSXTION',srv.state) STATE,
r.TOTAL_MESSAGES_DEQUEUED,
r.DEQUEUED_MESSAGE_NUMBER MSG_NUMBER,
to_char(r.DEQUEUE_TIME,'DD-MON-YY HH24:MI') DQ_TIME
FROM gV$STREAMS_APPLY_READER r, gV$SESSION s, DBA_APPLY ap, gV$STREAMS_APPLY_SERVER srv
WHERE r.SID = s.SID
and r.inst_id=s.inst_id and r.inst_id=srv.inst_id
AND r.SERIAL# = s.SERIAL#
AND r.APPLY_NAME = ap.APPLY_NAME
AND r.APPLY_NAME = srv.APPLY_NAME
UNION
SELECT apply_name,status, ' ' process_name, '(disabled)' state, NULL total_message_dequeued, NULL msg_number, NULL DQ_TIME
FROM DBA_APPLY
WHERE status != 'ENABLED'
/
set feedback on;

----chk_queues.sql
column QUEUE_NAME format a30
select QUEUE_NAME || ' n' || inst_id QUEUE_NAME,NUM_MSGS,SPILL_MSGS from gv$buffered_queues;