1 2 Previous Next 18 Replies Latest reply on Jan 18, 2013 10:57 AM by 985260 Go to original post
      • 15. Re: ORA 1555 - snapshot to old when querying over a DB link
        Dave Rabone
        Yes ... The result will interesting
        1 person found this helpful
        • 16. Re: ORA 1555 - snapshot to old when querying over a DB link
          I've run the test, is still failing.

          But if I run in the below order everything is ok. Any ideas why?

          Session 1 - select through dblink
          Session 2 - perform inserts/updates
          Session 1 - select from dual@dblink
          Session 1 - select through dblink
          • 17. Re: ORA 1555 - snapshot to old when querying over a DB link
            Dave Rabone
            Found the reference ... This is from the Administrators Guide for 10.2, Chapter 33, right at the end:

            Managing Read Consistency
            An important restriction exists in the Oracle Database implementation of distributed read consistency. The problem arises because each system has its own SCN, which you can view as the database 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.

            So my memory was nearly right. It's likely that your 01555s are caused by attempting a consistent read based on an old remote SCN, and the solutions are indicated in the last two sentances of the quote above.

            1 2 Previous Next