Oracle Database Discussions

Hi Guys,

I have a challenging question for you. I'm not an Oracle expert and I have an issue with a production database when executing a query over a DB Link - we encountered ORA-1555 snapshot to old. The query doesn't take too long, it normally ends in less than a second. The error happen only in week-ends, when the application is not used very much.

From what I read Oracle creates a distributed transaction and reserves an entry in the rollback segment when querying over a DB Link - please see http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

I've reproduced this error on the test server by reducing the UNDO_TABLASPACE size and set UNDO_RETENTION to 5 minutes.

Now I'm trying to fix it. The only solution that I have at the moment is to execute a COMMIT operation after each query that is using a DB_LINK. This doesn't look a very nice fix, it is more a hack.

Do you know a better solution? We don't need the distributed transaction that oracle automatically creates for this queries. We are just querying this database, we never insert/update/delete data.

I've tried SET TRANSACTION READ ONLY, doesn't help, the transaction is created.

Thank you very much!