ora-02020 too many database links in use

題記：之前在寫一個統計性質的儲存過程，執行時報了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

> show parameter open_links

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

2、連線6個dblink，每次連線後提交。則可以成功。

> show parameter open_links

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

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

no rows selected

> 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 );
  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.

> 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，每次連線後不提交。則提示失敗。

> 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 );
  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

> 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