前不久开发人员编译存储时报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

最新文章

  1. [转]Flash Player、AIR、Flex SDK 大全
  2. MySQL出现Access denied for user 'root'@'%' to database 'netai_test'问题
  3. Strust的基础情况
  4. HDU 3584 Cube
  5. Android 布局之LinearLayout 子控件weight权重的作用详析(转)
  6. 非阻塞IOserver型号
  7. oracle字段由中文前缀加数字，数字自动增长的实现
  8. 如何解决Linux 系统下 ifconfig 命令无网络接口 ens33
  9. IT男的别样人生，爱折腾，竟然辞职跑丽江去了
  10. EntityFramework+EntityFramework.SqlServerCompact部署网站
  11. 【Vue 2.x】计算属性
  12. python-简单的登陆接口
  13. MHA+ProxySQL实现读写分离高可用
  14. select 禁止 选择
  15. 使用ADO.NET访问、查询和操作数据库
  16. js-设置时间，获取几天后的时间
  17. kvm安装配置使用centos6.5
  18. FireDAC 下的 Sqlite [6] - 加密
  19. phonegap helloworld 之android
  20. shell应用技巧

热门文章

  1. Redis数据存储优化机制（转）
  2. 添加git忽略文件
  3. Angular学习笔记—路由（转载）
  4. Windows换行符问题
  5. Windows工作区目录创建
  6. 转：css中!important的作用
  7. hibernatetemplate find条件查询方法
  8. maven的相关命令
  9. netty5----心跳
  10. 【Head First Servlets and JSP】笔记24：include指令与include动作 & param动作 & foward动作