ORA-02020 too many database links in use重现

àì夳堔傛蜴生んèń 2022-08-08 11:47 178阅读 0赞

在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**