Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: Ora -00018 Error

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

  3. #3
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    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

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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;

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    hi,
    Thankx for the help.It was useful.

    mickykt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •