ora-02020 too many database links in use

关注

转载最新推荐文章于 2025-05-13 06:43:47 发布 208 阅读 AI 写同款· GEO 优化 ›

题记：之前在写一个统计性质的存储过程，运行时报了ORA-02020。研究下~

$ oerr ora 2020

02020, 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.

确实用了好几个database links 。

下面建立实验来验证 committing or rolling back the transaction 是ok的。

1、查看dblink的参数。创建多个dblink，这里我建立了6个：

从DBLINK_TEST1至DBLINK_TEST6

z@test10g> show parameter open_links

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

2、连接6个dblink，每次连接后提交。则可以成功。

z@test10g> show parameter open_links

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

z@test10g> col DB_LINK for a20
z@test10g> select * from v$dblink;

no rows selected

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

PL/SQL procedure successfully completed.

z@test10g> select * from v$dblink;

DB_LINK                OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
DBLINK_TEST1                 58 YES YES UNKN              0 NO NO                      1
DBLINK_TEST2                 58 YES YES UNKN              0 NO NO                      1
DBLINK_TEST3                 58 YES YES UNKN              0 NO NO                      1
DBLINK_TEST6                 58 YES YES UNKN              0 NO NO                      1

3、连接6个dblink，每次连接后不提交。则提示失败。

z@test10g> declare
2    v_i number;
3    v_sql varchar(500);
4 begin
5    for i in 1..6
6     loop
7      v_sql:='select count(*) from dual@DBLINK_TEST'||to_char(i);
8      execute immediate v_sql into v_i;
9      --commit;
10      dbms_output.put_line(i);
11     end loop;
12 end;
13 /
1
2
3
4
declare
*
ERROR at line 1:
ORA-02020: too many database links in use
ORA-06512: at line 8

z@test10g> select * from v$dblink;

DB_LINK                OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
DBLINK_TEST1                 58 YES YES UNKN              0 YES NO                      1
DBLINK_TEST2                 58 YES YES UNKN              0 YES NO                      1
DBLINK_TEST3                 58 YES YES UNKN              0 YES NO                      1
DBLINK_TEST4                 58 YES YES UNKN              0 YES NO                      1