Database Links and Read Only Databases

One of my colleagues asked me that he gets an error when he tries to query a table on our standby database. We use one of our physical standby database for reporting. It’s Oracle 10g, so we stop log apply and open the database in read only mode.

My colleague tried to run such an query:

He got “ORA-16000 database link read only query fails” error.

At first, I though that it’s about joining tables but after I simpled the query and tried to select from only one table but I still get the same error. Then I found the solution at Tom Kyte’s website.

Distributed stuff starts a transaction “just in case”.

So we should indicate that our transaction will not manipulate any row:

This will not prevent Oracle to create a new transaction, instead it establishes the current transaction as a read-only transaction.