ORA-00020: maximum number of processes (1000) exceeded 错误及解决办法

ERROR:
ORA-00020: maximum number of processes (1000) exceeded

--alert日志：

Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_cjq0_11338.trc:

ORA-00020: maximum number of processes 1000 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

Process m000 submission failed with error = 20

ORA-00020: maximum number of processes 1000 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process W000 submission failed with error = 20
Process W000 submission failed with error = 20
Process W000 submission failed with error = 20
Process W000 submission failed with error = 20
--查看listener.log 发现如下错误
* (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)
* (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
    
     
--此时已经登录不进库
ps -ef|grep LOCAL               --可以看到很多连接

使用os命令kill掉一些进程以释放资源     --head 取前10条ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|head|xargs kill -9
     
登陆后台数据库进一步观察会话使用情况
select event,program,count(*) from v$session group by
event,program order by 3;

EVENT            PROGRAM            COUNT(*)-DIAG idle wait                           oracle@lf1 (DIAG)                      
 1class slave wait        oracle@lf1 (GCR0)
                    1
buffer busy waits                        oracle@lf1 (J004)
                       1
library cache lock                 OMS                              59

从结果输出发现大量的会话处于library cache lock等待事件中，接下来定位阻塞者是哪个会话select event,p1raw,p2 from v$session where event='library
 cache lock';

EVENT                P1RAW                    P2-------------------- ---------------- ----------library cache lock   000000007C384160 1737585656library cache lock  

000000007C384160 1737462296library cache lock  

000000007C384160 1737338936library cache lock  

000000007C384160 1737215576library cache lock  

000000007C384160 1737092216library cache lock  

000000007C384160 1736968856
 

select event from v$session where
saddr=(select kgllkses from x$kgllk
 where KGLLKhdl='000000007C384160' and

KGLLKMOD>0);EVENT----------------------------------------------------------------log file switch (archiving needed)
   
至此已经看到了导致ORA-00020错误的源头，归档满了，删除多余日志问题解决！