OraFAQ Forum: Enterprise Manager

Home » RDBMS Server » Enterprise Manager » ora 00018

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora 00018 [message #244443]

Tue, 12 June 2007 18:30

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

Report message to a moderator

Re: ora 00018 [message #244445 is a reply to message #244443]

Tue, 12 June 2007 18:33

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ora 00018 [message #244462 is a reply to message #244443]

Wed, 13 June 2007 00:42

Michel Cadot
Messages: 68643
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

Report message to a moderator

Re: ora 00018 [message #244634 is a reply to message #244443]

Wed, 13 June 2007 10:07

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

Report message to a moderator

Re: ora 00018 [message #244653 is a reply to message #244634]

Wed, 13 June 2007 11:17

Michel Cadot
Messages: 68643
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

Report message to a moderator

Re: ora 00018 [message #244659 is a reply to message #244443]

Wed, 13 June 2007 12:21

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;

Report message to a moderator

Re: ora 00018 [message #244664 is a reply to message #244659]

Wed, 13 June 2007 12:31

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe.

Regards
Michel

Report message to a moderator

Re: ora 00018 [message #244680 is a reply to message #244443]

Wed, 13 June 2007 13:54

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ora 00018 [message #244682 is a reply to message #244680]

Wed, 13 June 2007 13:56

Michel Cadot
Messages: 68643
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

Report message to a moderator

Re: ora 00018 [message #244695 is a reply to message #244443]

Wed, 13 June 2007 16:45

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

Report message to a moderator

Re: ora 00018 [message #244698 is a reply to message #244443]

Wed, 13 June 2007 17:08

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: ora 00018 [message #244703 is a reply to message #244443]

Wed, 13 June 2007 17:48

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

Report message to a moderator

Re: ora 00018 [message #251473 is a reply to message #244462]

Fri, 13 July 2007 14:11

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

Report message to a moderator

Previous Topic:	Exporting Schema
Next Topic:	OEM database control

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 10:04:40 CDT 2024