RE: ORA-00018 issues

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@xxxxxxxxxxxxx 
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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:@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

Other related posts: