Home » RDBMS Server » Enterprise Manager » ora 00018
ora 00018 [message #244443] Tue, 12 June 2007 18:30 Go to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i have been working with sql and em for 3 hrs ago but now i cannot connect to either of them i keep having this error as stated below
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded


i tried changing my init.ora file from 150 to 300 as below
but still cannot connect
###########################################
# Processes and Sessions
###########################################
processes=300

pls can you help

Re: ora 00018 [message #244445 is a reply to message #244443] Tue, 12 June 2007 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>i tried changing my init.ora file from 150 to 300 as below

Did you stop & restart the database?
The only time the initSID.ora is read is at instance startup.
Re: ora 00018 [message #244462 is a reply to message #244443] Wed, 13 June 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00018: maximum number of sessions exceeded

Increase SESSIONS parameter (not PROCESSES).

Regards
Michel
Re: ora 00018 [message #244634 is a reply to message #244443] Wed, 13 June 2007 10:07 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i can't login to neither the em nor the sqlplus. at the moment i can't perform any task
Re: ora 00018 [message #244653 is a reply to message #244634] Wed, 13 June 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can always "/ as sysdba" this is not limite by any Oracle parameter.
But maybe you have an OS problem.
Post the error message.

Regards
Michel
Re: ora 00018 [message #244659 is a reply to message #244443] Wed, 13 June 2007 12:21 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
thanks for yor advice
do you think setting the session as below will work

ALTER SYSTEM SET sessions = 1000 SCOPE=SPFILE;
Re: ora 00018 [message #244664 is a reply to message #244659] Wed, 13 June 2007 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe.

Regards
Michel
Re: ora 00018 [message #244680 is a reply to message #244443] Wed, 13 June 2007 13:54 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>do you think setting the session as below will work
It depends upon your definition of work.
The value of SESSIONS/PROCESSES is only set at instance startup.
Changing the value within spfile is necessary, but not sufficient to raise the limit inside the instance.
Re: ora 00018 [message #244682 is a reply to message #244680] Wed, 13 June 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I didn't understand the question. I thought he just asked if the value is sufficient.

Regards
Michel
Re: ora 00018 [message #244695 is a reply to message #244443] Wed, 13 June 2007 16:45 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i TRIED USING THE SQL SYNTAX BELOW
ALTER SYSTEM SET SESSIONS = (1.1 * PROCESSES)+5 SCOPE = SPFILE
/

BUT I HAD ORA-02017: INTEGER VALUE REQUIRED.

INSTEAD OF
ALTER SYSTEM SET SESSIONS = 10000 SCOPE = SPFILE;
BCOS I WAS THINKING 1000 MIGHT BE TOO MANY
Re: ora 00018 [message #244698 is a reply to message #244443] Wed, 13 June 2007 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It is really too bad for us that you keep giving us a narative version of what you think is happening as opposed to using CUT & PASTE so we can see for outselves the reality.
You posts are entirely INCONSISTANT. If the following is true:
>i can't login to neither the em nor the sqlplus. at the moment i can't perform any task
then you should not be able to even get close to trying:
>ALTER SYSTEM SET SESSIONS = (1.1 * PROCESSES)+5 SCOPE = SPFILE

Keep in mind that as you increase SESSIONS/PROCESSES, the SGA will increase by default working set size TIMES the value of the increase.

The OS settings may not be able to handle this increase.

What OS name & version?
What version of Oracle to 4 decimal places?
Re: ora 00018 [message #244703 is a reply to message #244443] Wed, 13 June 2007 17:48 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
thanks for your support. I was able to resolve the login problem using '/ as sysdba' but not as user 'system' due to error
ora-00018.
I am still trying to get round this problem that is why i am doing this.
I thought if i can increase the session I will be able to overcome this.

I am using window xp, oracle 10.1.0.2
Re: ora 00018 [message #251473 is a reply to message #244462] Fri, 13 July 2007 14:11 Go to previous message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi all !

i think u didnt restart the database after changing in init.ora file regarding processes from 150 to 300.plz after restarting u find any trouble then check ur log files.
regards
Malik
Previous Topic: Exporting Schema
Next Topic: OEM database control
Goto Forum:
  


Current Time: Sat Dec 03 22:01:24 CST 2016

Total time taken to generate the page: 0.08567 seconds