ora-00020 error - # of processses exceeded

I am getting
ora-00020 error - # of processses exceeded
Oracle 8.1.6 resides on win200ok server.

I have increased the # of processes in init file from 200 to 600. It worked for 2 days fine, now its exceeding 600 proceeses and giving me ora-00020 error again.

I am using profiles to apply limits

sessions_per_user 8
cpu_per_session 1000000
cpu_per_call 1000000
connect_time unlimited
idle_time 60
logical_reads_per_session unlimited
logical_reads_per_call unlimited
composite_limit 2000000
private_sga unlimited

Still ora-00020 error happening. Is there anything apart from digging code to find any loose/open processes.

Any help is much appreciated.

-D

well you have to check with your developers if they close sessions when they finish any query or transaction

Quote:

---

Originally posted by pando
well you have to check with your developers if they close sessions when they finish any query or transaction

---

They are closing sessions - well most of the times. Do I need to move to MTS settings is there any more things I can try.

-D

if they close sessions there is no way you would get this kind of errors

Thats for sure developer or user are not closing sessions. Though you can implement DCD (Dead Connection Detection).Oracle recommends a value of approximately 10 (minutes) for the SQLNET.EXPIRE_TIME parameter. If it is not specified, or set to 0, then broken connections may be maintained indefinitely.

HTH

hi

i am also getting this sort of errors.

the oracle is clearing the connection
but the os doesn't kill the that connection.

when you look at the status of the session
they are shown as SNIPED.

even the kill from oracle
(alter system kill session .........)
has no effect.

then i had to generate the script to generate
a batch to use the orakill utility.

you can try that.

if you want i will send.


-Raja

Quote:

---

Originally posted by rajabalachandra
hi

then i had to generate the script to generate
a batch to use the orakill utility.

you can try that.

if you want i will send.


-Raja

---

Yes, Please send me the script. Thanks
-D

Is re-booting and option?
I think I saw this same thing in development once and a re-boot solved it.
One way or another your sessions are not getting cleared.

MH

What do you see in v$session and v$process ??
Are you using WIN performance monitor or Compaq Insight Manager ?

This may be of help.
http://metalink.oracle.com/metalink/..._id=213004.995

the content of my batch file raja.bat
=====================================


sqlplus scott@his/tiger @C:\HISKILL.SQL

c:\SNIPKILL.bat



the content of my script file c:\hiskill.sql
============================================

SET HEAD OFF;

SPOO C:\SNIPKILL.BAT;

SELECT 'ORAKILL HIS ' , B.SPID
FROM V$SESSION A, V$PROCESS B
WHERE A.PADDR = B.ADDR
AND A.STATUS='SNIPED';

SPOO OFF;


EXIT;



the sample output from the spool c:\snipkill.bat
================================================

ORAKILL HIS 770
ORAKILL HIS 545
ORAKILL HIS 378
ORAKILL HIS 534
ORAKILL HIS 339
ORAKILL HIS 703
ORAKILL HIS 748
ORAKILL HIS 600


8 rows selected.



i run the batch file raja.bat.

it executes the script hiskill.sql and the
batch file (snipkill.bat) spooled by
the the script.

in this script 'ORAKILL' is the utility you
can find in the \bin directory.

'HIS' is my database SID.
(You have to replace with your database sid).

the executing user should have select privilege over
v$session and v$process.


Note: You can run this batch file as scheduled task also.


-Raja

Quote:

---

Originally posted by daljitsb
I am getting
ora-00020 error - # of processses exceeded
Oracle 8.1.6 resides on win200ok server.

I have increased the # of processes in init file from 200 to 600. It worked for 2 days fine, now its exceeding 600 proceeses and giving me ora-00020 error again.

I am using profiles to apply limits

sessions_per_user 8
cpu_per_session 1000000
cpu_per_call 1000000
connect_time unlimited
idle_time 60
logical_reads_per_session unlimited
logical_reads_per_call unlimited
composite_limit 2000000
private_sga unlimited

Still ora-00020 error happening. Is there anything apart from digging code to find any loose/open processes.

Any help is much appreciated.

-D

---

Sound like a bug, is Oracle patched? What do you get after "SELECT * FROM V$VERSION;"