[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查询的.仅仅做一个记录.