ORA-02020 too many database links in use重现

最新推荐文章于 2024-05-09 22:55:56 发布

深圳gg

最新推荐文章于 2024-05-09 22:55:56 发布

阅读量5.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORA-系列

本文链接：https://blog.youkuaiyun.com/stevendbaguo/article/details/45739931

ORA-系列专栏收录该内容

61 篇文章

订阅专栏

本文探讨了在数据库连接管理中遇到的ORA-2020错误，详细介绍了OPEN_LINKS和OPEN_LINKS_PER_INSTANCE参数的作用及调整方法，并通过实验展示了如何通过修改参数值来解决数据库连接数限制的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在A库上建DB_link，更新B库。报ORA-2020 too many database links in use，是要设置OPEN_LINKS和OPEN_LINKS_PER_INSTANCE两个参数，它们的默认值都是4。
alter system set open_links=200 scope=both;
alter system set open_links_per_instance=200 scope=both;
问题来了，是修改A库中的参数还是B库，是修改A库的。
修改这类参数数据库要重启。

ORA-02020: 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 specifies the maximum number of concurrent open connections to remote databases in one session.

OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections globally for each database instance.

OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the number of connections from a session. The OPEN_LINKS parameter is not applicable to XA applications.

OPEN_LINKS_PER_INSTANCE跟OPEN_LINKS的区别是，前者是对于XA事务的。

做个试验重现一下：

在A库上：
SQL> show parameter open_link
NAME TYPE VALUE
----------------------------- ----------- --------
open_links integer 4
open_links_per_instance integer 4

create database link DBLINK_1
connect to TEST IDENTIFIED BY test
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
--按照同种方式建5个dblink

SQL> set serveroutput on
SQL> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..5
6 loop
7 v_sql:='select count(*) from dual@DBLINK_'||i;
8 execute immediate v_sql into v_i;
9 dbms_output.put_line('DBLINK_'||i);
10 commit;
11 end loop;
12 end;
13 /

DBLINK_1
DBLINK_2
DBLINK_3
DBLINK_4
DBLINK_5

PL/SQL procedure successfully completed

SQL>
SQL> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..5
6 loop
7 v_sql:='select count(*) from dual@DBLINK_'||i;
8 execute immediate v_sql into v_i;
9 dbms_output.put_line('DBLINK_'||i);
10 --commit;
11 end loop;
12 end;
13 /

DBLINK_1
DBLINK_2
DBLINK_3
DBLINK_4

declare
v_i number;
v_sql varchar(500);
begin
for i in 1..5
loop
v_sql:='select count(*) from dual@DBLINK_'||i;
execute immediate v_sql into v_i;
dbms_output.put_line('DBLINK_'||i);
--commit;
end loop;
end;

ORA-02020: 过多的数据库链接在使用中
ORA-06512: 在 line 9

SQL> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..4
6 loop
7 v_sql:='select count(*) from dual@DBLINK_'||i;
8 execute immediate v_sql into v_i;
9 dbms_output.put_line('DBLINK_'||i);
10 --commit;
11 end loop;
12 end;
13 /

DBLINK_1
DBLINK_2
DBLINK_3
DBLINK_4

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

--分两次执行
SQL> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..3
6 loop
7 v_sql:='select count(*) from dual@DBLINK_'||i;
8 execute immediate v_sql into v_i;
9 dbms_output.put_line('DBLINK_'||i);
10 --commit;
11 end loop;
12 end;
13 /

DBLINK_1
DBLINK_2
DBLINK_3

PL/SQL procedure successfully completed

SQL> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 4..5
6 loop
7 v_sql:='select count(*) from dual@DBLINK_'||i;
8 execute immediate v_sql into v_i;
9 dbms_output.put_line('DBLINK_'||i);
10 --commit;
11 end loop;
12 end;
13 /

DBLINK_4

declare
v_i number;
v_sql varchar(500);
begin
for i in 4..5
loop
v_sql:='select count(*) from dual@DBLINK_'||i;
execute immediate v_sql into v_i;
dbms_output.put_line('DBLINK_'||i);
--commit;
end loop;
end;

ORA-02020: 过多的数据库链接在使用中
ORA-06512: 在 line 9