RE: ORA-00018 issues
Date: Thu, 26 Feb 2009 23:21:38 +0200
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
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.comReceived on Thu Feb 26 2009 - 15:21:38 CST
> -----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:
> 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