TIP #17 : Handy queries for Stream administration (Capture process)

I found the following sql statements handy for capture administration in Stream :

-- If these views have no information, it might be because of capture process status .
-- Check capture status
select * from dba_capture;
exec dbms_capture_adm.stop_capturecapture_name=>'&CAPTURE_NAME');
exec dbms_capture_adm.start_capturecapture_name=>'&CAPTURE_NAME');

-- To check captured SCN and applied SCN
-- Applied SCN is the most recent SCN which was dequeued with relevent apply process.
-- This SCN is important because all redo logs should be kept until apply process.
select * from dba_capture;

-- To view parameter :
select * from dba_capture_parameters;

-- To change parameter :
-- Value of parameter always should be Varchar2 even if the value parameter is number.
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => '_CHECKPOINT_FREQUENCY',value=>1);
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => '_SGA_SIZE',value=>20000000);
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => 'PARALLELISM',value => '3' );
END;
/

--- Changing _SGA_SIZE may cause ORA-01341: LogMiner out-of-memory problem.Be careful of this value.Its default value is 10MB.
-- To view statistics about capture process :
select * from V$STREAMS_CAPTURE;

-- To find any error relates to capture process
-- Check alter log.
-- Enable trace level of capture process :
select * from dba_capture_parameters;
exec dbms_capture_adm.set_parameter('&capture_name','trace_level','2');

-- To find out which rules capture process is capturing :
-- Brief capture rules :
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name);

-- Which ruleset is working for capture process
select rule_set_name from dba_capture where capture_name='&capture_name';

-- Which rules are in ruleset name
select * from "DBA_RULE_SET_RULES" where rule_set_name='&rule_set_name';

-- Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

-- Which global rules the capture process is capturing
select * from "DBA_STREAMS_GLOBAL_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

---Capture process latency
--- Latency_second is the number of seconds between when an event was recorded in the redo log and when the event was enqueued by the capture process.
--- The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently enqueued event.
--- The enqueue time, which is when the capture process enqueued the event into its queue.

SELECT (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS,TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBERFROM V$STREAMS_CAPTUREWHERE CAPTURE_NAME = '&capture_name';

--- The redo log scanning latency, the number of seconds between the creation time of the most recent redo log event scanned by a capture process and the current time.
--- The seconds since last recorded status, which is the number of seconds since a capture process last recorded its status--- The current capture process time, which is the latest time when the capture process recorded its status.
--- The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently captured event.

SELECT ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIMEFROM V$STREAMS_CAPTUREWHERE CAPTURE_NAME = '&capture_name';