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.

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

1 Comment

  1. Mohammed

    Hi Team,

    How to Apply archive log file gaps in standby database.

    Let me know the steps.


    Mohammed Siddiq

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.