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.

How to Make a Table Read-only in Oracle

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:

To make it writable again, issue the following command:

In previous versions of Oracle, there was no command to make a table read-only, but we can use a little trick to do it. All we need is to create a constraint and make it disable:

Because the constraint is disabled, it is not enforced but it will not let you change the data to ensure that data still conforms to the constraint’s requirements. Unfortunately, direct path loads will bypass this, so we also need to issue the following command:

This will also prevent user to drop this table. Another option is to mark the tablespace which contains the table as read-only, but it’s not practical because it wll make all the objects read-only residing in the tablespace.