?
Solved

What is meaning of this error message?

Posted on 2008-06-10
26
Medium Priority
?
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 12
26 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 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 143

Expert Comment

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

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 143

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 143

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 143

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 143

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 143

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
 

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 143

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 143

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 143

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 143

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 143

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

765 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