?
Solved

How to increase maximum number of processes? I got this ORA-00020: maximum number of processes (150) exceeded

Posted on 2007-10-14
10
Medium Priority
?
25,077 Views
Last Modified: 2013-12-19
Hi,

I don't really have Oracle background. I'm using Oracle 9i. May I know how to increase maximum number of processes? I need to increase it to 400. I got this ORA-00020: maximum number of processes (150) exceeded. Thanks.
0
Comment
Question by:rospcc
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 20076324
connect as user with DBA privileges

SQL> conn / as sysdba
SQL> alter system set processes = 200 scope=both

using "scope=both" in "alter system" command means, that system variable is changed immediately and is written to spfile - the value will remain as you have changed across oracle sthutdown and startup
0
 
LVL 9

Accepted Solution

by:
konektor earned 1600 total points
ID: 20076335
hmm, now i've checked http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams168.htm#sthref698 - the parameter is not modifiable. so:
1.
try alter system set processes=200 scope=spfile and restart database
or if doesn't work
2.
create pfile from spfile
edit pfile, chnge value of processes
create spfile from pfile
shutdown
rename created spfile to actual spfile which will be used at startup
startup
0
 

Author Comment

by:rospcc
ID: 20076348
I can't execute the code, it gives me this error: ORA-02095: specified initialization parameter cannot be modified.

I did connect as sysdba. What could be the issue?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:rospcc
ID: 20076353
Sorry, I'm a total newbie in Oracle. May I know how to create pfile from spfile? Where to get this sp file?
0
 

Author Comment

by:rospcc
ID: 20076410
Sorry, how do we know if we've set the processes correctly? Is there any command to check the current number of processes? Tried Select processes from SYSTEM, but seems like that's not the right command.
0
 
LVL 6

Assisted Solution

by:makhan
makhan earned 400 total points
ID: 20076510
Hi,

Use the command below

select name, type, value
 from v$parameter
 where name = 'processes'


0
 

Author Comment

by:rospcc
ID: 20076560
Managed to get it changed. Thanks for all your help.
0
 
LVL 3

Expert Comment

by:mganesh
ID: 20076625
Hi rospcc,
Let me try to be as simple as possible.

This parameter is not modifiable online. So a database restart is required for this change to be effected.

Your database could have either been running with the pfile (a text file) or an spfile (binary file) as the base.

First find out how your database has been started:
SQL> show parameter spfile;
If you get the path of the spfile in the output, then the db is running with an spfile. Else, it has been started with a pfile

I. If using an spfile:

SQL> alter system set processes=500 scope=spfile;
SQL> shutdown immediate;
Login again
SQL> startup;

2. If not using an spfile:

Locate the pfile. The default location of the pfile is:
Under windows :  $ORACLE_HOME\database
Under Unix/Linux: $ORACLE_HOME/dbs
The name of the pfile is usually of the form init<database SID>.ora

Edit the processes parameter to 500
Login to SQL as sysdba
SQL> shutdown immediate
Login again
SQL> startup pfile ='<path to your pfile>'

Finally verify the current value of the parameter by,
SQL> show parameter processes.

Be sure to modify the sessions parameter aslo to a value equal to: (1.1*processes)+5
This is a thumb rule, your requirements could vary though.

Thats it !

Regards,
Ganesh

0
 
LVL 3

Expert Comment

by:mganesh
ID: 20076631
OK
I think I was a couple of minutes late. Please ignore my post
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

840 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