ORA-02020 too many database links in use重现

在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