mysql建立dblink跨库查询_[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查询的.仅仅做一个记录.