视图使用多个DBLINK导致ORA-16000: database open for read-only access

在Oracle ADG环境中，当使用的存储过程、视图中具有调用DBLINK的代码时，或反复执行带DBLINK的查询语句时，会遇到报错：ORA-16000: database open for read-only access
这是由于ADG环境为只读状态，使用一次DBLINK后必须提交或回滚事务后，才能再执行第二次的DBLINK调用。

Oracle官方文档及MOS文档中对此也有明确说明，如https://docs.oracle.com/cd/E11882_01/server.112/e25494/start.htm#ADMIN11154中Opening a Database in Read-Only Mode章节中Limitations of a Read-only Database部分明确指明：

"When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link.
This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only."
在相关MOS文档中也有对应测试案例，如
如Dblink on Physical standby - ORA-16000 (Doc ID 1296288.1)文档
Query with multiple dblinks in ADG / read-only database fails with ORA-16000 (Doc ID 2462936.1)

如视图或存储过程中使用了两个DBLINK就会报错，如类似如下报表查询想放在ADG上执行以减轻主库的性能压力，就会报错ORA-16000: database open for read-only access：

  CREATE OR REPLACE FORCE VIEW "test"."T_REPORT" (………) AS    select from test.test_REPORT@testzrzk a,mpi.test222@test222 b where a.pid=b.pid and a.TIME>to_char(sysdate-40,'YYYYMMDDHHMISS')
  或
select * from table@dblink1
union
select * from table@dblink2;
如我们通过DBLINK访问DG备机时，遇到报错可以设置只读事务：

SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
READ ONLY PHYSICAL STANDBY
 
SQL> select count(*) from aa@test_link;
select count(*) from aa@test_link
*
ERROR at line 1:
ORA-16000: database open for read-only access
 
SQL> set transaction read only;
Transaction set.
 
SQL>select count(*) from aa@test_link
count(*)
----------
1