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
Solved

What is meaning of this error message?

Posted on 2008-06-10
26
313 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 143

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 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2007 standard - defrag (eseutul /d) 10 57
SYbase 4 36
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 64
Creation date for a PDB 5 39
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

789 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