Notes on Researching ORA-02049 Errors

If disabling trigger with dblink is possisble, then disable it.

If disabling the trigger is not possible, then one should look into the following to troubleshoot the issue:

Is there a bit map used in the involved tables?

Are there any open transactions on these tables? Check for lock, block, and wait on these tables.

Is there materialized view with refresh on commit setting on these tables?

How fast transactions can be posted using dblink?

Check with Oracle Support to see if DISTRIBUTED_LOCK_TIMEOUT can be changed to a new higher value (the default is 60) - 300 seconds is a common value in a distributed environment. 

~~~~

Query the V$LOCK View:

• Any ANALYZE TABLE TABLENAME COMPUTE STATISTICS? (not a factor 10.2+)
• Any SELECT FOR UPDATE on the table?

~~~~

Managing Read Consistency

An important restriction exists in Oracle's implementation of distributed read consistency. The problem arises because each system has its own SCN, which you can view as the database's internal timestamp. The Oracle database server uses the SCN to decide which version of data is returned from a query.

The SCNs in a distributed transaction are synchronized at the end of each remote SQL statement and at the start and end of each transaction. Between two nodes that have heavy traffic and especially distributed updates, the synchronization is frequent. Nevertheless, no practical way exists to keep SCNs in a distributed system absolutely synchronized: a window always exists in which one node may have an SCN that is somewhat in the past with respect to the SCN of another node.

Because of the SCN gap, you can execute a query that uses a slightly old snapshot, so that the most recent changes to the remote database are not seen. In accordance with read consistency, a query can therefore retrieve consistent, but out-of-date data. Note that all data retrieved by the query will be from the old SCN, so that if a locally executed update transaction updates two tables at a remote node, then data selected from both tables in the next remote access contain data prior to the update.

One consequence of the SCN gap is that two consecutive SELECT statements can retrieve different data even though no DML has been executed between the two statements. For example, you can issue an update statement and then commit the update on the remote database. When you issue a SELECT statement on a view based on this remote table, the view does not show the update to the row. The next time that you issue the SELECT statement, the update is present.

You can use the following techniques to ensure that the SCNs of the two machines are synchronized just before a query:

• Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example, SELECT * FROM DUAL@REMOTE.
• Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before issuing the remote query.