开发人员反映数据库报错:
Error: ORA-04052: 在查找远程对象 IRMS20.T_ORDER_HUIZONG@JIAKELINK 时出错
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-02020: 过多的数据库链接在使用中
1.参考文档:
ORA-02020 Error when Using Database Links in a Lookup (文档 ID 463403.1)
2.查看错误介绍
[oracle@sddwdb1 ~]$ oerr ora 202002020, 00000, "too many database links in use"// *
Cause: The current session has exceeded the INIT.ORA open_links maximum.// *
Action: Increase the open_links limit, or free up some open links by//
committing or rolling back the transaction and canceling open//
cursors that reference remote databases.
从 错误介绍原因是open_links参数超过了最大限制,open_link参数表示一个会话可以打开的dblink的最大连接数,11g中数据库默认是4,从错误原因得知这里出现错误应该就是单个会话使用的dblink太多,而数据库参数设置的太小了出现的问题。
参考文档:Error 'Ora-02020 Too Many Database Links In Use' When Trying To Run Workbooks After Database Upgrade (文档 ID 832046.1)
3.下面来进行模拟测试
(1)配置tnsnames.ora、创建dblink
zynx=(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
dw =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
kf =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
sms =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
sjfx =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
create user zjtest identified by zj_TEST_0107 account unlock default tablespace users;
grant dba to ZJTEST;
create public database link zynxtest connect to zjtest identified by "zj_TEST_0107" using 'zynx';
create public database link dwtest connect to zjtest identified by "zj_TEST_0107" using 'dw';
create public database link kftest connect to zjtest identified by "zj_TEST_0107" using 'kf';
create public database link smstest connect to zjtest identified by "zj_TEST_0107" using 'sms';
create public database link sjfxtest connect to zjtest identified by "zj_TEST_0107" using 'sjfx';
(2)测试连接
select table_name from user_tables@zynxtest
union allselect table_name from user_tables@dwtest
union allselect table_name from user_tables@kftest
union allselect table_name from user_tables@smstest
union allselect table_name from user_tables@sjfxtest
可以看到参数设置是4,而一个会话中使用4个dblink是没有问题的,下面来使用5个dblink:
可以看到出现错误了,这是一个sql中使用5个dblink,下面来测试一个会话中5个sql使用5个dblink:
可以看到单个sql使用一个dblink,只要是在同一个会话中超过5个不同的远端连接就不行。
因为一个会话打开dblink连接之后,只要会话不退出,dblink连接就不关闭,所以只要是一个会话不管几个sql都是不行的。
(3)在所有库删除创建的测试link、测试用户
drop user ZJTEST cascade;
drop public database link zynxtest;
drop public database link sjfxtest;
drop public database link dwtest;
drop public database link kftest;
drop public database link smstest;
增加open_links参数值,这个参数是静态参数,修改之后需要重启数据库。