Solved

What is meaning of this error message?

Posted on 2008-06-10
26
310 Views
Last Modified: 2013-12-19
Hi,
  I am getting the below error message while execute the SQL statement.

ORA-00020: maximum number of processes(%s) exceeded
         What is reason of this error message? I have seen some forums. There they are mentioned we have to change the process parameter. How we can handle this? Please anyone can explain me??
0
Comment
  • 14
  • 12
26 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21749833
run this query:
select * from v$parameter where name like '%processes%'

it will return, among others, the line with name = processes.
that value is defined in init.ora (pfile) resp the spfile for the database.

there are 2 solutions:
* increase that parameter value until the problem goes away (note: setting it too high will waste memory, as each process will take a certain amount from the memory pool)
* decrease the number of processes running (ie connections).
  -> this is the preferred method in the first place, as it will solve the underlying problem, and eventually other performance issues along
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21749902
Hi angel thanks for you reply. Can you tell me how to decrease the number of processes running? Please explain me.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749914
let's first start with your current value of processes, what is the value?
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21749942
       This is my current value.
show parameter processes

aq_tm_processes                      integer  1
db_writer_processes                  integer  1
job_queue_processes                  integer  2
log_archive_max_processes            integer  2
processes                            integer  200
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749949
200 should be fine...
what gives this query:
select username, count(*) 

from v$session

group by username

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21749961
It gives like below
USERNAME          COUNT(*)
APPLSYSPUB    21
APPS           170
             8
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749975
as you can see, the USER APPS takes up 170 connections... which is the "main" problem, unless you rally have 150+ active users sitting around.
if that is the case, you will have to increase the processes to a higher value.
if not, you should instead check out the application(s) to release the connections (faster).
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21749995
   Yes i know that angel. Everyone is accessing APPS schema. But I can't control that. And you tell me which one is better for increasing process to higher value or decrease the process connections. And i am not able to understand the below statement

--->if not, you should instead check out the application(s) to release the connections (faster).

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750037
if you don't understand that, I have a problem :) ---
I speak about the application code, which opens the connection to the oracle database and runs some sql statements... often, developers forget to properly close the connections ASAP in their code ...

anyhow, do you know how do change the init.ora setting?
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750064
Yes ok i understood that. And another main thing is i am not able to find init.ora file in my oracle directory. I searched it. But i am not getting it. Or else shall i give the below command.

alter system set processes = 300 scope=spfile;
              If i this give command will it create any problem like memory waste, low speed etc.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750096
>   If i this give command will it create any problem like memory waste, low speed etc.
in short: no

you will need to restart the oracle db for the parameter value to be applied.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750114
Is there any problem to restart oracle DB?? And please tell me which is the best way??
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750174
>Is there any problem to restart oracle DB??
obviously, any user being connected will be "dropped", and any non-committed transaction will be rolled back.

>And please tell me which is the best way??
there is only 1 way: shutdown + startup.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750182
ok can you tell the procedure of shutdown and startup. Because i am using toad application. Please guide me.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750211
Hi i have given the below command. But i am getting the following error. Why and What is reason?
SQL> alter system set processes=300 scope=spfile;
alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750238
>Because i am using toad application.
you cannot do that from toad. you have to use SQL*Plus command line for example, best on the oracle machine itself.

in regards to the error, that means you must have the init<SID>.ora file in the $ORACLE_HOME$/dbs folder ...
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750291
Sorry angel this file is not available in my ORACLE_HOME folder. But it will be created when we installed oracle database in the machine. But i don't know how it will happen? Or can you tell me how to create spfile?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750448
spfile can only be created when you have the pfile.
so, you MUST have either the init<SID>.ora or the  spfile<SID>.ora.

what does
show parameter spfile

return?
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750711
 This is result when i execute the above query

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750936
and this:

show parameter pfile

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21750965
This also the same result
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21751059
what are the folders you have under $ORACLE_HOME$ (on the oracle server itself)
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21751216
There are so many folders. Which folder i have to mention?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21751262
database or dbs
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21752066
Ya angel now it is working fine. One of our DBA has done this. But i don't know who has done this. He did not increase the parameter value. But he reduced the number of processing. Now the following query is giving
select username, count(*)
from v$session
group by username

USERNAME          COUNT(*)
APPLSYSPUB    9
APPS           114
             8

Anyway thanks for your cooperation.


0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31465675
Nice solution Angel.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article describes some very basic things about SQL Server filegroups.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now