Re: ORA-00018 issues

Maureen,

Don't forget that you have a history of utilization of "sessions" (and other resources) in STATS$RESOURCE_LIMIT in STATSPACK (i.e. Oracle9iR2 and below) and DBA_HIST_RESOURCE_LIMIT in AWR (i.e. Oracle10gR1 and above), so you can get a good idea of when things started to go crazy for sessions in the instance(s).  For example, using AWR you can use something like...
select   to_char(s.end_interval_time,'DD-MON HH24:MI') tm,
         r.current_utilization,
         r.max_utilization,
         r.initial_allocation
from     dba_hist_snapshot s,
         dba_hist_resource_limit r
where    r.resource_name = 'sessions'
and      s.snap_id = r.snap_id
order by 1;
Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


Bobak, Mark wrote: 
Simple test:

Reboot box, get back to ground zero.
Now, login, do:
Show parameter sessions
Select count(*) from v$session;


Do not give up your established connection!
Now, have the consultant login/attempt to login.

If he gets the error, repeat the two commands above:
Show parameter sessions
Select count(*) from v$session;


Compare the results.  I'd expect the value of sessions is not going to change.  (I'm pretty sure it can't chance w/o a bounce.)  However, I'd be interested to know what difference you see in the count(*) from v$session query results.

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Maureen English
Sent: Thursday, February 26, 2009 1:26 PM
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;url="">

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
-- http://www.freelists.org/webpage/oracle-l

Other related posts: