ORA-00020: maximum number of processes (%s) exceeded

Question: 

we encounter the below problem yesterday morning:

(1) All running batch application hang running half-way

(2) Attempt to login via sqlplus giving below error:
 

ERROR:

ORA-00020: maximum number of processes (%s) exceeded

REMARK:
current parameter value for PROCESSES = 300


(3) Attempt to connect as sys to shutdown the database giving the below:
Connected to an idle instance.

(at this point, we check that all background
processes like pmon, smon, etc still exists)

(4) the instance later being terminated by LGWR, & we manage to startup (without
need to shut it down)

**********************************************************

referring to item (3), is anyone out there able to provide me a PRECISE explanation for what does it mean here "connected to an idle instance" in such a scenario?

Answer by Mark:

"Connected to an idle instance" usually means that you connected "AS SYSDBA" and the instance you connected to is not started.

However, if your instance is out of processes, then a non-SYSDBA connection will error out with ORA-0020, and a SYSDBA connection will error out with "Connected to idle instance." It's a little misleading, but that's what happens.

Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there.
 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值