yi
asked on
OERR ORA 20 - maximum number of processes exceeded
Hi,
I am using Oracle8i and I've encountered this problem often in my application - ORA00020 : max number of processes exceeded.
I know modifying the init.ora to increase the number of processes can be a solution, but I want to find out the real cause of the problem.
Thanks.
I am using Oracle8i and I've encountered this problem often in my application - ORA00020 : max number of processes exceeded.
I know modifying the init.ora to increase the number of processes can be a solution, but I want to find out the real cause of the problem.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes -- each connection counts, so that counts as 4 connections...
You should be able to have a fair number of concurrent connections with that process setting.. It wouldn't hurt to up it a bit... However, it will only take effect when the database is restarted (processes parameter is not dynamically set)...
On disturbing thing --- Be sure to close your application connections when your done using them... We had an ASP application that spawned a lot of connections and never closed them. They remained and took up process slots -- thus keeping users from connecting to the database.
A few other suggestions, that may help....
in your SQLNET.ORA parameter on the server -- add the line:
SQLNET.EXPIRE_TIME = 10
This parameter is for SQL*Net Dead Connection Detection --- The parameter is specified in minutes (in the above example, it is 10 minutes... DCD is intended primarily for environments in which clients power down their systems without disconnecting from their Oracle sessions, a problem characteristic of networks with PC clients.... Basically, the server sends a small probe packet is sent from server to client at a user defined interval (usually several minutes). If the connection is invalid (usually due to the client process or machine being unreachable), the connection is closed when an error is generated by the send operation, and the server process terminates the connection. Thus freeing up a process slot.
Mind one thing -- The default value for sqlnet.expire_time is OFF -- so you must set it....
Another thing you might wish to set. is a client Profile with the parameter IDLE_TIME.
If you have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users (and thus freeing up the processes)
The IDLE_TIME parameter limits periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
So, there are some thigns that you must do to make this work:
1) in the init.ora file, add the following parameter:
RESOURCE_LIMITS = TRUE
If you cannot shutdown the database, in the meantime you issue the command:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
this does not alleviate the need to add the line to the init.ora
2) Log into the database w/ a DBA account, and issue the following command:
CREATE PROFILE <name>
LIMIT IDLE_TIME = <NUMBER>
where NAME is the Profile name, such as USER_IDLE_LIMIT
and the IDLE_TIME is in number of minutes --- say 1 Hour:
example:
CREATE PROFILE USER_IDLE_LIMIT
LIMIT IDLE_TIME = 60;
3) Assign the profile to the desired user with the following command:
ALTER USER <username> PROFILE <profile name>.
example:
alter user SCOTT profile USER_IDLE_LIMIT;
This should also help free up those resources...
I hope you find these suggestions helpful.
Sincerely,
dBalaski
You should be able to have a fair number of concurrent connections with that process setting.. It wouldn't hurt to up it a bit... However, it will only take effect when the database is restarted (processes parameter is not dynamically set)...
On disturbing thing --- Be sure to close your application connections when your done using them... We had an ASP application that spawned a lot of connections and never closed them. They remained and took up process slots -- thus keeping users from connecting to the database.
A few other suggestions, that may help....
in your SQLNET.ORA parameter on the server -- add the line:
SQLNET.EXPIRE_TIME = 10
This parameter is for SQL*Net Dead Connection Detection --- The parameter is specified in minutes (in the above example, it is 10 minutes... DCD is intended primarily for environments in which clients power down their systems without disconnecting from their Oracle sessions, a problem characteristic of networks with PC clients.... Basically, the server sends a small probe packet is sent from server to client at a user defined interval (usually several minutes). If the connection is invalid (usually due to the client process or machine being unreachable), the connection is closed when an error is generated by the send operation, and the server process terminates the connection. Thus freeing up a process slot.
Mind one thing -- The default value for sqlnet.expire_time is OFF -- so you must set it....
Another thing you might wish to set. is a client Profile with the parameter IDLE_TIME.
If you have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users (and thus freeing up the processes)
The IDLE_TIME parameter limits periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
So, there are some thigns that you must do to make this work:
1) in the init.ora file, add the following parameter:
RESOURCE_LIMITS = TRUE
If you cannot shutdown the database, in the meantime you issue the command:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
this does not alleviate the need to add the line to the init.ora
2) Log into the database w/ a DBA account, and issue the following command:
CREATE PROFILE <name>
LIMIT IDLE_TIME = <NUMBER>
where NAME is the Profile name, such as USER_IDLE_LIMIT
and the IDLE_TIME is in number of minutes --- say 1 Hour:
example:
CREATE PROFILE USER_IDLE_LIMIT
LIMIT IDLE_TIME = 60;
3) Assign the profile to the desired user with the following command:
ALTER USER <username> PROFILE <profile name>.
example:
alter user SCOTT profile USER_IDLE_LIMIT;
This should also help free up those resources...
I hope you find these suggestions helpful.
Sincerely,
dBalaski
I would not increase the parameter in the init.ora file because with only 8 concurrent users, that will only be masking a greater problem that will bite you later, as you already pointed out.
It's likely that your Java code is not closing connections, or opening too many. Some Java developers leave the closing of connections up to garbage collection, but that can take quite awhile to happen.
It's likely that your Java code is not closing connections, or opening too many. Some Java developers leave the closing of connections up to garbage collection, but that can take quite awhile to happen.
bkowalski -- I already mentioned that to him in my message (about the opening and closing connections) in the third paragraph..
dB
dB
dbalaski, I know, I saw that before I posted my comment.
dbalaski, let me explain. I thought my comment added more information and a different opinion.
I suggest:
not bumping up the PROCESSES parameter at all (different); and
Java developers may say that Garbage Collection will take care of the open connections, but Garbage Collection can take awhile, and it's still a problem if they are opening too many within the same block of code (more info)
I suggest:
not bumping up the PROCESSES parameter at all (different); and
Java developers may say that Garbage Collection will take care of the open connections, but Garbage Collection can take awhile, and it's still a problem if they are opening too many within the same block of code (more info)
The first step is to determine what are those processes. Login SYSTEM and execute.
select * from v$session.
This tells you what processes is running, OS User, Database User, machine ID and even whether it is active.
You better to start application on one machine and use above query to determine how many sessions are created and left open.
From the above result, you may determine it is application issue. Ask the developer to change it with the proof on your hand! Profile should only be used to control human usage. If it is application issue, profile doesn't help.
Steve
select * from v$session.
This tells you what processes is running, OS User, Database User, machine ID and even whether it is active.
You better to start application on one machine and use above query to determine how many sessions are created and left open.
From the above result, you may determine it is application issue. Ask the developer to change it with the proof on your hand! Profile should only be used to control human usage. If it is application issue, profile doesn't help.
Steve
ASKER
Thanks for the explanation. The reason why I do not want to modify the init.ora file is because there are only about 8 clients each using JDBC codes to access the database, and I think 50 processes is sufficient.
To clarify one point, if I have 1 client machine and I run 2 SQLPlus sessions and start 2 application database connections via JDBC (without closing connection), I shall have altogether 4 (2+2) connections from this client machine at the Server side ?
Regards.
Yi