Queries for Oracle Stream administration

handy queries for Stream administration (Propagation process)

Basically in propagation, two views are important : DBA_QUEUE_SCHEDULES and
DBA_PROPAGATION.
The followings are handy statements for propagation administration of stream

--- Disable propagation
begin 
dbms_aqadm.disable_propagation_schedule('STRMADMIN.STREAMS_QUEUE', 'OEMREP.US.ORACLE.COM');
end;
/
--- Enable propagation
begin 
dbms_aqadm.enable_propagation_schedule('&source_queue_name_with_owner', '&database_link'); 
end;
/
-- Info about propagation
select * from dba_propagation;
-- Find out source and destination propagation
SELECT p.SOURCE_QUEUE_OWNER '.'p.SOURCE_QUEUE_NAME '@'g.GLOBAL_NAME "Source Queue",p.DESTINATION_QUEUE_OWNER '.'p.DESTINATION_QUEUE_NAME '@'p.DESTINATION_DBLINK "Destination Queue"FROM DBA_PROPAGATION p, GLOBAL_NAME g;
-- Find propagation parameters
SELECT s.*FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION pWHERE p.PROPAGATION_NAME = 'STRMADMIN_PROPAGATE'AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;
---- How to change parameters-- Propagation job sets to 15min to propagates events every 15 minutes
-- Each propagation lasting max 300 second
-- 25 second wait before new events in a completely propagated queue are propagated
BEGIN
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(queue_name => '&source_queue_name',destination => '&database_link_name',duration => 300,next_time => 'SYSDATE + 900/86400',latency => 25);
END;
/

-- Find out progpagation rule set
SELECT RULE_SET_OWNER, RULE_SET_NAMEFROM DBA_PROPAGATIONWHERE PROPAGATION_NAME = '&propagation_name';
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='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation 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='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation 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='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name);

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

-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES" where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');
-- Which global rules the capture process is capturing
select * 
from "DBA_STREAMS_GLOBAL_RULES" 
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');
------ More about propagation job
SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,s.PROPAGATION_WINDOW,s.NEXT_TIME,s.LATENCY,
DECODE(s.SCHEDULE_DISABLED,'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED,s.PROCESS_NAME,s.FAILURES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;
-------- Total number of bytes which was propagated.
SELECT s.TOTAL_TIME_IN_sec, s.TOTAL_NUMBER, s.TOTAL_BYTES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;