ora-00020 maximum number of processes 150 exceeded [message #586658] |
Sun, 09 June 2013 02:08 |
|
kimberlyg2007
Messages: 2 Registered: June 2013
|
Junior Member |
|
|
I don't know if there is a beginner forum out there for PL SQL, but I cannot find one.
I just started taking an Intermediate Oracle course to learn PL SQL. Today is my first day and I just installed Oracle 11g. I was in SQL Plus, tried the edit command. I got the message "wrote file Afiedt.buf" and then it was just a flashing cursor in the line below. I could not type any commands. I just hit the X to close the program.
What could I have done to exit the program with that flashing cursor since I was unable to type anything?
[Updated on: Sun, 09 June 2013 02:20] Report message to a moderator
|
|
|
Re: ora-00020 maximum number of processes 150 exceeded [message #586659 is a reply to message #586658] |
Sun, 09 June 2013 02:36 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
It could be due to two reasons:-
1. Too many defunct Oracle processes. You would need to force kill all of them at OS level.
create a script like this:-
alias osprod='ps -ef | grep user | grep -v defunct'
osprod | grep -v pbrun | grep -v vi | grep -v crontab | grep -v ksh | grep -v "ps -ef" | grep -v grep | cut -c9-17
osprod | grep -v pbrun | grep -v vi | grep -v crontab | grep -v ksh | grep -v "ps -ef" | grep -v grep | cut -c9-17 | xargs kill -9
2. You might have to increase the processes parameter. If it is too less then you may get the ORA-00020 error as the natural result in growth of system usage. This won't be that simple step to do, you ought to be careful with these parameters. You will have to increse the SGA size and SESSION_CACHED_CURSORS to 100.
I would suggest before doing anything please read the docs and understand thoroughly.
|
|
|
|
Re: ora-00020 maximum number of processes 150 exceeded [message #586661 is a reply to message #586660] |
Sun, 09 June 2013 03:31 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 09 June 2013 13:35
@Lalit Kumar B,
1/ Always ask for the OS before posting something that might be irrelevant code.
2/ What is the relation between ORA-00020 and SESSION_CACHED_CURSORS or SGA size?
1/ Ok, agreed.
2/ Per OERR, details for the ORA-00020 error:-
ORA-00020: maximum number of processes (string) exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter
So, If he wishes to rethink about the initialization parameters, then he would have to change these parameters too. Not directly related to ORA-00020, agree.
Anyway, the 2 points which I mentioned as the reason for this error holds true irrespective of the OS.
1. Too many defunct Oracle processes. You would need to force kill all of them at OS level.
2. Too less process parameter.
@Michel, Please add if you have anything more.
|
|
|
Re: ora-00020 maximum number of processes 150 exceeded [message #586662 is a reply to message #586661] |
Sun, 09 June 2013 03:59 |
|
Michel Cadot
Messages: 68651 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:So, If he wishes to rethink about the initialization parameters, then he would have to change these parameters too.
No, he wouldn't.
This is a beginner question, you confuse people with so much (weakly related) information.
Please be concise and straight. Address ONLY the current problem then, and only then, you might extend the initial question when OP has understood his issue.
Too much information kills the information. When I read your post the things I will do is increase SGA size and SESSION_CACHED_CURSORS (why this one?) but not PROCESSES and so still have the problem (above all if I am on Windows and can't run your point 1).
Regards
Michel
[Updated on: Sun, 09 June 2013 04:00] Report message to a moderator
|
|
|
|
|
Re: ora-00020 maximum number of processes 150 exceeded [message #586692 is a reply to message #586690] |
Sun, 09 June 2013 13:13 |
|
Michel Cadot
Messages: 68651 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The current issue is (most likely) not a SQL one.
You didn't answer my question which is mandatory to investigate in your problem and provide you some hints to solve it:
Quote:What is your OS? Post OS type, version, service pack, and edition (for instance Windows XP SP3 Professional or result of uname -a and Unix interface)?
Regards
Michel
|
|
|