ORA-02020 too many database links in use

测试环境中报ORA-02020 too many database links in use

 

由于开发人员不太了解db_link的根本目的，在测试库中建了很多db_link

用于访问同实例中不同用户的数据。

其实在同一个实例的多个用户之间相互访问用grant 和建同义词synonym的方式更好;

如： grant select on '&table_name' to  '&user_name';

       select 'create or replace synonym ' || table_name || ' for &user_name' ||'.'||
       table_name || ' ;'
       from user_tables
       where table_name = '&table_name';

 

 

SQL> show parameter open_links

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

 

  

 

SQL> select count(1) from dba_db_links;

  COUNT(1)
----------
         9

 

解决方法：

SQL> alter system set open_links=10 scope=spfile;

 

 

关于ORA 2020 的一些错误信息说明

 

 

Error:  ORA 2020
Text:   too many database links in use
-------------------------------------------------------------------------------
Cause:  The maximum number of active connections to remote databases per user
        login has been reached.
Action: If the user has no open cursors, the current SQL statement accesses
        more than the maximum allowed remote databases.
        Otherwise, the user may free remote database connections by closing
        all cursors that access the databases.
        If this occurs often, consider increasing the value of the
        initialization parameter OPEN_LINKS, which controls the maximum number
        of concurrent open connections to remote databases per user process.

 

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.