ORA-00020: maximum number of processes exceeded 

One place report to start with the ORA-00020 investigation. hope the following output helps to figure out the issue.

connect to the database and spool the output and analyze.

  

set markup html on spool on 

SPOOL Resources.HTML 

set pagesize 200 

set echo on 

select systimestamp from dual; 

select * from v$version; 

select * from v$instance; 

select * from v$resource_limit; 

show parameter processes 

show parameter job 

select 

s.sid, 

s.serial#, 

p.spid, 

s.username "V$SESSION - USERNAME", 

p.username "V$PROCESS - OS USERNAME", 

p.terminal, 

p.program, 

s.command, 

s.event, 

s.status, 

s.server, 

s.process, 

s.machine, 

s.terminal, 

s.program 

FROM v$session s,v$process p 

WHERE p.addr=s.paddr 

order by p.background desc; 

SELECT 

p.spid , 

s.username , 

s.osuser , 

s.status , 

s.sid , 

s.program 

FROM 

v$process p, 

v$session s 

WHERE s.paddr(+)=p.addr 

Order by status; 

select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ; 

select count(*) from v$session; 

select count(*) from v$process; 

select module,count(1) from v$session 

group by module 

order by 2; 

select PROGRAM,count(1) from v$process 

group by PROGRAM 

order by 2; 

select username,status,count(*) from v$session group by username,status order by 3,1; 

select addr, spid, username, program, background 

from v$process 

where addr not in (select paddr from v$session) 

/ 

SELECT USERNAME,PROFILE,INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS ; 

select * from dba_profiles; 

select PROFILE,count(*) from dba_users group by PROFILE; 

select a.snap_id,a.begin_interval_time begin,a.end_interval_time end,b.* 

from DBA_HIST_SNAPSHOT a join DBA_HIST_RESOURCE_LIMIT b on a.snap_id=b.snap_id 

where a.BEGIN_INTERVAL_TIME between trunc(sysdate-3) and trunc(sysdate)+1 

and b.RESOURCE_NAME in ('processes','sessions') order by a.snap_id; 

spool off 

set markup html off spool off  


so what's next ? refer - ORA-00020 error