RE: ORA-00018 issues

From: Tanel Poder <tanel_at_poderc.com>
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:

1)

select

    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
where

    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.

--
Regards,
Tanel Poder
http://blog.tanelpoder.com


 





> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org 

> [mailto:oracle-l-bounce_at_freelists.org] 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 10.2.0.3.0 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

> rl=jdbc:oracle:thin:_at_mydb.mydomain.xxx;1234:MYDB

> 

> where mydb.mydomain.com 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

> 

> --

> http://www.freelists.org/webpage/oracle-l

> 

> 


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 26 2009 - 15:21:38 CST