[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.

SCOTT@book> select sysdate from dual@loop;
SYSDATE
-------------------
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
                         *
ERROR at line 1:
ORA-16000: database open for read-only access

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查詢的.僅僅做一個記錄.