RE: ORA-00018 issues

From: Tanel Poder <>
Date: Thu, 26 Feb 2009 23:21:38 +0200
Message-ID: <DEE5881BE33E4BDBBEB54BCC33076010_at_porgand>

I saw some answers already, including the part that sessions high water mark (from v$resource_limit right?) was less than max sessions and v$session shows only 16 sessions.

Here's my take.

Btw, I think this is not related to running out of process state objects as this would result in ORA-00020.

v$session does not show all session slots in use. For example recursive sessions are hidden in v$session.

There are two ways for checking out what's happening (you need to start a DBA session first, then connect with the java program to cause this problem).

Then run this to identify hidden sessions:



    to_char(ksspaflg, 'XX') state

  , to_char(ksuseflg, 'XXXXXXXX') flag_hex
  , ksuudlna logon_user
  , ksuudnam current_user
  , ksuseltm logon_time
  , ksusepid client_pid
  , ksuseunm os_user
  , ksusemnm machine
  , ksusepnm program

from x$ksuse

    bitand(ksspaflg,2) != 2
and indx not in (select sid from v$session) and ksuseflg > 0

Or take a systemstate dump to see to which process the allocated sessions belong:

2) alter session set events 'immediate trace name systemstate level 2';

...and grep for "(session)" to list all session state objects.

Tanel Poder


> -----Original Message-----
> From:
> [] On Behalf Of Maureen English
> Sent: 26 February 2009 20:26
> To: oracle-l
> Subject: ORA-00018 issues
> We're experiencing a very strange problem...ORA-00018 errors.
> Yes, I know that the error means 'maximum number of sessions
> exceeded', but that is not the case.
> We are running Oracle on Solaris 10. We have a
> consultant who is working in this database, using a
> connection string like:
> jdbc:cp:uPortalPooled;driver=oracle.jdbc.driver.OracleDriver;u
> where is the machine and MYDB is the database SID.
> We're trying to figure out if his attempts to connect are
> messing things up, or if there is something else going on.
> If we reboot the machine, the problem disappears -- we can
> connect via sqlnet.
> Then the consultant tries his connection, gets the error and
> we can no longer connect via sqlnet.
> Any suggestions for further troubleshooting? We're looking
> into java issues, as well as system/database issues, but so
> far nothing is obvious.
> - Maureen
> --
Received on Thu Feb 26 2009 - 15:21:38 CST

Original text of this message