Database Software

Hi,

I'm replicating changes (database-wide) between two databases (one-way only) using STREAMS.

The whole thing is working fine, except when some large operations are executed in source DB (it seems Streams doesn't handle big transactions well).

When this happens, there's a big delay in target database until it synchronizes again.

However, I'd like to get some information about what's going on (SQL executed, operations...) but the queries I've tried aren't returning any info for me:

select * from V$STREAMS_TRANSACTION;

select * from v$logmnr_contents;

select xid, xidusn, xidslt, xidsqn, mining_status from v$logmnr_transaction where mining_status = 'ACTIVE';

select distinct xid from v$logmnr_transaction where mining_status = 'ACTIVE';

select distinct sql_id from v$active_session_history where xid in (select distinct xid from v$logmnr_transaction where mining_status = 'ACTIVE');

select sql_text from dba_hist_sqltext where sql_id in (select distinct sql_id from v$active_session_history where xid in (select distinct xid from v$logmnr_transaction where mining_status = 'ACTIVE'));

I've tried running LogMiner but couldn't find what I expected (operations in source database were split in batches of 200.000 LCRs, but couldn't find any info about them).

So, having these kind of messages in source DB alert.log, what queries (or logmining procedure) should I run to obtain more detailed info about those large t?

Mon Jun 27 12:36:52 2016

CP01: long running txn detected, xid: 0x0005.026.0003e493 (5.38.255123)

Mon Jun 27 12:37:07 2016

CP01: large txn committed (199999 LCRs), xid: 0x0005.026.0003e493 (5.38.255123)

Mon Jun 27 13:17:46 2016

CP01: long running txn detected, xid: 0x0005.042.0003e484 (5.66.255108)

Mon Jun 27 13:18:02 2016

CP01: large txn committed (199999 LCRs), xid: 0x0005.042.7fff0003e484 (5.66.255108)

Mon Jun 27 13:58:08 2016

CP01: long running txn detected, xid: 0x0005.01a.0003e3c5 (5.26.254917)

Mon Jun 27 13:58:26 2016

CP01: large txn committed (199999 LCRs), xid: 0x0005.01a.0003e3c5 (5.26.254917)

Thanks and regards,