Select across dblink from a dataguard database

From: "Ben Wittmeier" <Ben.Wittmeier@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@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@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?

Thanks,
Ben






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.

Select across dblink from a dataguard database

Other related posts: