Error message ORA-00020

I got ORA-00020 error message saying that NUMBER OF PROCESSES (75) EXCEEDED.
- (75) is just a eg.
I read that one can increase the number of processes in init.ora to avoid this. Stop and start the service. But this keeps happening again and again.
Is there any way that I can get into database to see who is running what to kill some dead sessions, look for locking ?
Is there any query or batch processes which will tell me in advance if it is goingto crash.
I tried system manager, sys/password ...

Thanks
Sonali

What version of Oracle are you using ?
You definitely can increase the value of PROCESSES to much higher that 75; but then you said that 75 was just an example so I assume that you have already tested with higher values.

Did you look into enabling the Multi-Threaded-Server Configuration ? This will allow more user processes to connect thru a smaller number of server processes.

For the Session and Lock info you may look into the following views --

v$session
v$lock


If you have OEM, then it has a nice tool called "Top Sessions" that shows you the user sessions in a detailed fashion.

- Rajeev

I have Oracle 8 and Oracle 8i both.
What is the max size of processes you can set ???
Does it have to do something with number of licenses ?
Thanks again

the value is OS dependant.

For 8i on NT the value can be anything greater than 5 ( 6 to unlimited)

In one of my 8i database this is set to 200

- Rajeev

i am running a Oracle8i database and my number of processes is set to 1000. (no i am not running multithreaded server)

The init.ora parameter "processes=nn" determines how many concurrent
connections you can have to your database at any given time. On the
unix box if you were to do a "ps -ef | grep -i oracle | grep -i local " you
can see how many connections you have at any given time. You can also
query the dynamic view "v$process" table to find out how many proceses
you have at any given time. You can modify the init.ora file and increase
the parameter however you have to make sure that your semaphores
set accordingly in the etc/system file otherwise the instance won't start
for lack of semaphores. At the unix prompt you can type "ipcs -b" and
it will give you the list of the semaphores usage. The following is the list
from my server:

IPC status from as of Sat Jan 27 06:18:10 2001
T ID KEY MODE OWNER GROUP QBYTES
Message Queues:
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 900 0x4bbc3048 --rw-rw---- oracle dba 151150592
m 1201 0xced4ac68 --rw-rw---- oracle dba 151150592
m 802 0x04a87278 --rw-rw---- oracle dba 151150592
m 3 0x50000575 --rw-r--r-- root root 68
m 604 0x9a9fffc0 --rw-rw---- oracle dba 37445632
T ID KEY MODE OWNER GROUP NSEMS
Semaphores:
s 589824 00000000 --ra-ra---- oracle dba 100
s 1376257 00000000 --ra-ra---- oracle dba 100
s 1179650 00000000 --ra-ra---- oracle dba 100
s 1900547 00000000 --ra-ra---- oracle dba 100

the sum of the last column under the semaphores is the total number
of processes in my system.

Could you post /etc/system file?
You need to increase SEMMNS parameter.

Onestly, I don't know what SEMMNS parameter is ?
Where can I find... /etc/system file?
What is semaphores ? Where and how can I find it for NT ?

Thanks

I think that the first thing you need to check is the max processes allowed by the OS. Depending on your OS you can find it by doing a "vi /etc/services."

The default max.processes is usually set very low, when I had this problem last week the OS max processes was set to 1024 and I was able to increase it to 90,000. I am running Linux 6.2. But that is where I would check first.

Hope it helps :)

Doug

u should first check /etc/system
as tamils mentioned u should make sure if your os allows more than the number of processes mentioned..

I am enclosing the formula:

set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=x
set semsys:seminfo_semmns=y
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767

x = #of processes available (refer init.ora) plus 10 per instance.
As of now they are 100 processes plus 10 since it is 1 instance.
so x = 110

y = double the #of largest processes parameter available plus 10 per database.
As of now, 100 processes x 2 = 200 plus 10 per database
so y = 210

The above are 2 of the few parameters need to be set in the /etc/system file.
Log in as root and check to make sure the modifications suits your requirement.

Once u are done in Unix, then change the init.ora and bounce the db. I guess you should be all set..

I have another concern:
If I have 100 processes set, are even the 10 background processes included or excluded in the count..

Thanks