3 Replies Latest reply on Jun 26, 2012 6:53 AM by 916935

    How to kill concurrent request oracle session which is terminated

    916935
      Dear,

      In our production environment Concurrent request "Gather Schema Stats" was running 2 days. Hence we cancelled the request.
      Request completed as terminated status. Unfortunately the oracle session of that concurrent request went to "Killed" status. Still utilizing CPU resource.

      If we try, "alter system kill session 'SID,serial#';"
      it gives below message,

      ERROR at line 1:
      ORA-00031: session marked for kill


      Could you please provide steps to kill this oracle session.

      Instance detail:

      Oracle Applications R12.1
      Oracle database 11.1.0.7

      Thanks,
      ...basha

      Edited by: 913932 on Jun 25, 2012 2:32 AM
        • 1. Re: How to kill concurrent request oracle session which is terminated
          RKA
          Hi,

          You may need to wait for the session to get killed.
          May it would be reading undo records. Check that if it reads undo or not.
          • 2. Re: How to kill concurrent request oracle session which is terminated
            Hussein Sawwan-Oracle
            In our production environment Concurrent request "Gather Schema Stats" was running 2 days. Hence we cancelled the request.
            Request completed as terminated status. Unfortunately the oracle session of that concurrent request went to "Killed" status. Still utilizing CPU resource.

            If we try, "alter system kill session 'SID,serial#';"
            it gives below message,

            ERROR at line 1:
            ORA-00031: session marked for kill
            Try "alter system kill session 'SID,serial#' immediate".
            Could you please provide steps to kill this oracle session.
            Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running. [ID 735119.1]

            Thanks,
            Hussein
            • 3. Re: How to kill concurrent request oracle session which is terminated
              916935
              Dear,

              I did try "Alter system kill session 'sid,serial#' immediate;" but session still remained in "KILLED" status for more than 3 days.

              Forcefully i killed the unix process spid from V$process. Now the session got killed and expired.

              Steps followed:
              1. Obtained SID and SPID from given concurrent request id.

              SELECT ses.sid,ses.status,ses.serial#,pro.spid
              FROM v$session ses, v$process pro
              WHERE ses.paddr = pro.addr
              AND pro.spid IN (SELECT oracle_process_id
              FROM fnd_concurrent_requests
              WHERE request_id = &request_id);

              2. from unix prompt, killed unix process using spid obtain from above query.
              #kill -9 <SPID>


              Thanks for the response.