[20170511]DBLINK跨庫查詢遇到ORA-16000

[20170511]DBLINK跨庫查詢遇到ORA-16000.txt

--//如果在備庫通過dblink跨庫查詢,如果在一個事務查詢涉及2個dblink,會遇到ora-16000錯誤,做一個記錄:

1.環境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//建立dblink.

CREATE PUBLIC DATABASE LINK LOOPBACK USING 'localhost:1521/book';

CREATE PUBLIC DATABASE LINK LOOP USING 'localhost:1521/book';

2.測試:

--//重新啟動資料庫,設定隻讀.

SYS@book> startup open read only

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

Database opened.

--//以scott使用者:

SCOTT@book> select sysdate from dual@loopback;

SYSDATE

-------------------

2017-05-11 08:44:52

SCOTT@book> select sysdate from dual@loop;

select sysdate from dual@loop

                         *

ERROR at line 1:

ORA-16000: database open for read-only access

--//如果送出就不出現錯誤.

SCOTT@book> commit ;

Commit complete.

2017-05-11 08:46:01

3.如果1條語句包含2個dblink連接配接,問題依舊無法避開.

SCOTT@book> select sysdate from dual@loopback,dual@loop;

select sysdate from dual@loopback,dual@loop

SCOTT@book> host oerr ora 16000

16000, 00000, "database open for read-only access"

// *Cause:  The database was opened for read-only access.  Attempts to

//          modify the database using DML or DDL statements generate this

//          error.

// *Action: In order to modify the database, it must first be shut down and

//          reopened for read/write access.

--//我記憶裡10g好像在隻讀的資料庫,無法使用dblink查詢的.僅僅做一個記錄.