2 Replies Latest reply: Dec 12, 2012 12:48 AM by Salman Qureshi RSS

    Max procees and session reached.

    user9256814
      Hi Team,

      Recently we faced issue, max session reached. so we decided to kill the session and killed some the session. currently max session is 613 only, but max process reached value is 871. since session status is in'KILLED' is ther any chance of it is not releasing the process(PID) in the OS level and max process reached ?.

      we are facing error ORA-12516. Please help me on this.

      SQL> select count(*) from v$session where status='KILLED';

      COUNT(*)
      ----------
      118

      RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
      ------------------------------ ------------------- --------------- ------------------------------ -------------
      processes 871 896 900 900
      sessions 613 878 995 995

      Thanks.
        • 1. Re: Max procees and session reached.
          Niket Kumar
          SET LINESIZE 132
          UNDEFINE sid
          COLUMN username heading "Username" format a20
          COLUMN username heading "Username" format a20
          COLUMN status heading "Status" format a20
          COLUMN sid heading "SID" format 999999
          COLUMN serial# heading "Serial#" format 999999
          COLUMN spid heading "OS PID" format 999999
          COLUMN osuser heading "OS USer" format a20
          COLUMN program heading "Program" format a30
          SELECT a.username
          , a.status
          , a.sid
          , a.serial#
          , b.spid
          , a.osuser
          , b.program
          FROM v$session a
          , v$process b
          WHERE a.paddr=b.addr(+)
          AND a.status='INACTIVE'
          /



          kill process through unix kill command using SPID .
          • 2. Re: Max procees and session reached.
            Salman Qureshi
            Hi,
            As Niket suggested, run query provided by him to have OS Process ID of the processes which have been killed and use unix command "kill -9 <PROCESS_ID>" to release the session. Please correct the query and fine session with status KILLED instead of INACTIVE
            SELECT a.status
            , b.spid
            FROM v$session a
            , v$process b
            WHERE a.paddr=b.addr
            AND a.status='KILLED'
            /
            Salman