• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

What is meaning of this error message?

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
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 14
  • 12
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi angel thanks for you reply. Can you tell me how to decrease the number of processes running? Please explain me.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's first start with your current value of processes, what is the value?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Suriyaraj_SudalaiappanAuthor Commented:
       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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
200 should be fine...
what gives this query:
select username, count(*) 
from v$session
group by username

Open in new window

0
 
Suriyaraj_SudalaiappanAuthor Commented:
It gives like below
USERNAME          COUNT(*)
APPLSYSPUB    21
APPS           170
             8
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
   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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>   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
 
Suriyaraj_SudalaiappanAuthor Commented:
Is there any problem to restart oracle DB?? And please tell me which is the best way??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Suriyaraj_SudalaiappanAuthor Commented:
ok can you tell the procedure of shutdown and startup. Because i am using toad application. Please guide me.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
 This is result when i execute the above query

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and this:

show parameter pfile

Open in new window

0
 
Suriyaraj_SudalaiappanAuthor Commented:
This also the same result
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what are the folders you have under $ORACLE_HOME$ (on the oracle server itself)
0
 
Suriyaraj_SudalaiappanAuthor Commented:
There are so many folders. Which folder i have to mention?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
database or dbs
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Nice solution Angel.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now