Views, monitoring and troubleshooting streams

Views, monitoring and troubleshooting
select capture_name, queue_name, ERROR_NUMBER, ERROR_MESSAGE
from dba_capture where status != 'ENABLED'

select * FROM DBA_CAPTURE;
select * from dba_propagation;

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;

#May safely remove these logs
SELECT * FROM DBA_LOGMNR_PURGED_LOG;

SELECT * FROM DBA_CAPTURE_PARAMETERS;
SELECT * FROM DBA_CAPTURE_EXTRA_ATTRIBUTES
Capture troubleshooting--View capture statusSELECT c.capture_name, SUBSTR (s.program, INSTR (s.program, '(') + 1, 4) process_name, c.SID, c.serial#, c.state, c.total_messages_captured, c.total_messages_enqueued, c.enqueue_time last_enqueue, sysdateFROM v$streams_capture c, v$session sWHERE c.SID = s.SID AND c.serial# = s.serial#;Capture rules--Do not rely on DBA_STREAMS_TABLE_RULES, if you uncleanely dropped --STRMADMIN user or something, old unused rules will be thereselect DBA_RULES.* from dba_capture, DBA_RULE_SET_RULES, DBA_RULES where DBA_RULE_SET_RULES.rule_set_name (+)= dba_capture.rule_set_nameand DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name
#Is there a capture history?select * from DBA_HIST_STREAMS_CAPTURE
Propagationselect * from dba_propagation where propagation_name = 'PROPAGATION_TEST'Propagation rulesselect DBA_RULES.* from dba_propagation, DBA_RULE_SET_RULES, DBA_RULES where DBA_RULE_SET_RULES.rule_set_ name (+)= dba_propagation.rule_set_nameand DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name

Troubleshooting NO DATA FOUND in apply for update/delete

ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SYS.LCR$_ROW_RECORD", line 419
ORA-06512: at "MTS_APPLY.TABLE_HANDLERS", line 32
ORA-06512: at line 1
Remember that primary key columns on destination are always evaluated.
You'll get your job very easy using this query:
select tc.owner, tc.table_name, tc.column_name,
a.COMPARE_OLD_ON_DELETE, a.COMPARE_OLD_ON_UPDATE, decode(k.column_name, null, 'N', 'Y') manual_key_column
from dba_tab_columns tc, DBA_APPLY_TABLE_COLUMNS a, dba_apply_key_columns k
where
a.OBJECT_OWNER (+)= tc.owner and a.OBJECT_NAME (+)= tc.TABLE_NAME and a.COLUMN_NAME (+)= tc.COLUMN_NAME
and k.OBJECT_OWNER (+)= tc.owner and k.OBJECT_NAME (+)= tc.TABLE_NAME and k.COLUMN_NAME (+)= tc.COLUMN_NAME
and owner='MTS_OWNER'
and table_name='AGENTS'
#set pk manually, use null on column_list to reset
execute DBMS_APPLY_ADM.SET_KEY_COLUMNS(object_name => 'MTS_OWNER.AGENTS', column_list => 'ID,NAME');
select * from dba_apply_key_columns
#set old values to be compared, default is true for all columns
execute DBMS_APPLY_ADM.COMPARE_OLD_VALUES(object_name => 'MTS_OWNER.AGENTS', column_list => '*', operation => '*', compare => false);
select * from DBA_APPLY_TABLE_COLUMNS order by 1, 2, 3

Untested
Remove stream Metalink note 276648.1
Multi version data dictionary refer to Metalink note 212044.1

Remove/Uninstall Streams
#10g
begin
DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
end;
SELECT * FROM DBA_QUEUE_TABLES order by owner, queue_table
SELECT * FROM DBA_QUEUES order by owner

SELECT * FROM DBA_APPLY_DML_HANDLERS
select * from DBA_QUEUE_SCHEDULES
select * from DBA_STREAMS_COLUMNS;
select * from DBA_STREAMS_ADMINISTRATOR;
select * from DBA_STREAMS_RULES;
select * from DBA_STREAMS_TABLE_RULES;
select * from SYS.DBA_STREAMS_UNSUPPORTED;