Hi all,

I have a portal site running on a database using Oracle.This was the error that was show Ora-00018 "maximum Number of sessions exceeded".This was about Aug 14 9:15

While i queried the v$session and V$license i found the following info
Session=335(init.ora value)
Processes=300(init.ora)
Session_highwater=327(v$license)

Next i listed the alert and trace files:

If u check this file u can find that trace files have been created from the Aug 13 20:55 till Aug 14 23:20.(Pls refer to first attachment).

Next i have attaced a portion of the alert log file.

In both alert file and trace files we can find that the error 00018 existed from Aug 13 to Aug 14.But why it only reflected on the 14th.

I also am a bit worried why is this happening so.Can any one pls help me in this issue.
Also the Oracle Expert does not recommend to increase the Process or Sessions init values

I would appreciate any help.
I also forgot to tell u that the problem was solved when we restarted the appache server for the application

Thank you
mickykt

Is your app releasing connections correctly, we use Apache/tomcat and the sometimes a developer will forget to release a connection which causes the connection pool to slowly increase until it reaches the process limit.

Alan

Hi Thanks for the responce,

HOw can i find whether the application is releasing the sessions correctly.When i take the v$session i find that a lot of inactive sessions are present.

But how do i know whether is this being used or not.

I found that the web site came up yesterday after we restarted the appache server.So i also feel that the application is holding up sessions.But how to prove on this pont.

THanks for the help

mickykt

OK heres a query I use to identiy dead connections, it also shows the last sql executed by the connection. If you have a problem with some code not releasing the connection back to the connection pool then you will see lots of connections probably executing the same sql.

NOTE change the 'and last_call_et > 10000' which is the number of seconds SINCE the connection was last used, change it to an appropriate value for your situation.

Alan

select logon_time, last_call_et "time inactive",nvl(s.username, 'ORACLE PROCESS') username, s.machine, s.program,
s.sid session_id, s.status,
sql_text, ss.value "CPU used",
trunc(buffer_gets/(executions+1)) "BUFF-EXEC", trunc(buffer_gets/(rows_processed+1)) "BUFF-ROWS",first_load_time, executions, parse_calls, disk_reads, buffer_gets, rows_processed
from v$session s,
v$sesstat ss,
v$statname sn,
v$sqlarea sa
where s.sid = ss.sid and
ss.statistic# = sn.statistic# and
sn.name = 'CPU used by this session' and
s.sql_address = sa.address and
s.sql_hash_value = sa.hash_value and last_call_et > 10000
order by machine, status, program, last_call_et asc;

hi,
Thankx for the help.It was useful.

mickykt