Oracle DBLink连接数过多的问题(Ora-02020)

前不久开发人员编译存储时报ORA -02020 错，如下是解决方案步骤.

报错全信息：

Error:OR A -04052在查: 找远程对象 NIP.PB_PERADDRESSLIST@DB_NIP 时出错

ORA-00604 : 递归 SQL 级别 1 出现错误

ORA-02020 : 过多的数据库链接在使用中

查看一下有关link的参数

SQL> show parameter open_links

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

open_links integer 4

open_links_per_instance integer 4

SQL>

参数的解释：

***open_links :每个session最多允许的dblink数量;

***open_links_per_instance:指每个实例最多允许的dblink个数

扩大允许使用dblink 量；

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

System altered

SQL> alter system set open_links_per_instance=50 scope=spfile;

System altered

SQL> show parameter open_links

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

open_links integer 4

open_links_per_instance integer 4

SQL>

重启后生效...

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 293601280 bytes
Fixed Size                  1248600 bytes
Variable Size              96469672 bytes
Database Buffers          188743680 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL>

参数检查：

SQL> show parameter open_links

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

open_links integer 50

open_links_per_instance integer 50

SQL>

在编译就OK咧.

----------------------

How to view open dblink connections (Doc ID 387848.1)

GOAL

To find out the number of database links opened.

SOLUTION

OPEN_LINKS
Default: 4 Range: 0 to 255
Specifies the maximum number of concurrent open connections to remote databases in one session.
These connections include database links plus external procedures and cartridges each of which uses a separate process.

The following view shows the database link connections that are currently open in your current session:

V$DBLINK - Lists all open database links in your session, that is, all database links with the IN_TRANSACTION column set to YES.

NOTE: It is important to state that the section above "Lists all open database links in your session" is important, as this is only YOUR open dblinks that can be seen.

For example, you can create and execute the script below to determine which links are open (sample output included):

COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

SQL> @dblink

DB_LINK OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
INST2.ACME.COM 0 YES YES UNKN 0 YES YES 255

Note that above displays ONLY details about database links open in the session within which you are working.

If looking for details about database links open by different sessions, might use below:

sqlplus /nolog

connect / as sysdba

select username, osuser, status, sid, serial#, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte );

USERNAME OSUSER STATUS
------------------------------ ------------------------------ --------
SID SERIAL#
---------- ----------
MACHINE
----------------------------------------------------------------
PROCESS TERMINAL
------------------------ ------------------------------
PROGRAM
------------------------------------------------
SCOTT bugmnt INACTIVE
68 11
celclnx1.us.oracle.com

29318 pts/15
sqlplus@celclnx1.us.oracle.com (TNS V1-V3)

REPADMIN bugmnt INACTIVE
232 5
celclnx1.us.oracle.com

28081 pts/14
sqlplus@celclnx1.us.oracle.com (TNS V1-V3)

SQL>

-----------------------------------------------

SQL> alter system set open_links=255 scope=spfile;
alter system set open_links=255 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> alter system set open_links=255 scope=both;
alter system set open_links=255 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set open_links_per_instance=255 scope=spfile;
alter system set open_links_per_instance=255 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> alter system set open_links_per_instance=255 scope=both;
alter system set open_links_per_instance=255 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified