RE: ORA-00018 issues
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <sxmte@xxxxxxxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- 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:
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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
