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;

oracle queries

oracle

Audit invalid logon attempts

audit create session whenever not successful;
set linesize 120
column OS_USERNAME format a20
column USERHOST format a20
column TERMINAL format a20
column CLIENT_ID format a20
select * from dba_audit_session where returncode != 0;
noaudit create session whenever not successful;

more on auditing

logging table

CREATE TABLE log_messages (
id NUMBER NOT NULL
,message varchar2(255) not null
,logged_time date not null
,username varchar2(38) not null
,sid number not null
) TABLESPACE app_support;

CREATE SEQUENCE log_message_id;

CREATE OR REPLACE PROCEDURE log_msg (p_message IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_sid NUMBER;
BEGIN
SELECT sid INTO l_sid FROM v$mystat WHERE ROWNUM=1;
INSERT INTO log_messages (id, message, logged_time, username, sid) VALUES (log_message_id.nextval, p_message, SYSDATE, USER, l_sid);
COMMIT;
END;
/

AQ coalesce

Metalink note 271855.1 has a script aqcoalesce.sql
To quote from the note
The procedure performs the following operations relating to AQ objects

alter table AQ$_ < QUEUE_TABLE_NAME > _X coalesce;
where X=I (dequeue), T (time_manager), and H (history) IOTS for multi-consumer queue tables and

alter index AQ$_ < QUEUE_TABLE_NAME > _Y rebuild;
where Y=I (dequeue), and T (time-manager) indexes for single-consumer queue tables

oracle - tracing

session
on: dbms_system.set_ev(sid,serial#,10046,level,'');
off: dbms_system.set_ev(sid,serial#,10046,0,'');
system wide
on: alter system set events '10046 trace name context forever, level ';
off: alter system set events '10046 trace name context off';

levels
4=binds
8=waits
12=binds and waits

oracle - High Water Mark

Metalink article 262353.1

essentially

select count (distinct dbms_rowid.rowid_block_number(r.rowid)) "used blocks", blocks "below hwm", empty_blocks "above hwm"
from r, dba_tables
where table_name = '&table_name'
group by blocks, empty_blocks;