Select across dblink from a dataguard database
Date: Mon, 30 Jun 2008 11:07:25 -0600
We have a 10g dataguard database that is in read-only mode during the day. We want to use it for reporting purposes. The dataguard database has a database link going out to another 'regular" database. Whenever a query from a report utilizes the dblink, it results in an ORA-16000 error (database open for read-only access).
As per asktom (
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8908 307196113 ), "distributed stuff starts a transaction just in case", so even though the dataguard db is in read-only mode, we still need to execute a "set transaction read only" command prior to using the dblink - see below:
SQL> select count(*) from attributes_at_some_database; select count(*) from attributes * ERROR at line 1: ORA-16000: database open for read-only access SQL> set transaction read only; Transaction set. SQL> select count(*) from attributes_at_some_database; COUNT(*) ---------- 1056897
What we would like to do is automate the 'set transaction read only' command so that a report never runs into this problem. The only idea I have right now is to have a logon trigger that executes the 'set transaction read only' command. Not sure if that will work or not.
Anyone have any ideas on how to deal with this?
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.Received on Mon Jun 30 2008 - 12:07:25 CDT