ORA-02020 too many database links in use

最新推荐文章于 2024-05-09 22:55:56 发布

原创最新推荐文章于 2024-05-09 22:55:56 发布 · 5.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#database #integer #user #table #initialization #sql

本文探讨了ORA-02020错误的原因及解决方案，详细介绍了当数据库链接使用过多时如何调整参数，并提供了替代方案如使用GRANT权限和同义词SYNONYM。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试环境中报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.