How To Ensure Terminated conc program freed DB/OS Sessions

FND_CONCURRENT_REQUESTS table Codes meanings..

Often while trouble shooting with the concurrent requests table we may wonder what does status_code and phase_code internally, meaning of the codes in the status_code and phase_code columns of the FND_CONCURRENT_REQUESTS table are as follows.... 

STATUS_CODE Column:

==================

Code Meaning

A Waiting

B Resuming

C Normal

D Cancelled

E Error

F Scheduled

G Warning

H On Hold

I Normal

M No Manager

Q Standby

R Normal

S Suspended

T Terminating

U Disabled

W Paused

X Terminated

Z Waiting

PHASE_CODE column:

==================

Code Meaning

C Completed

I Inactive

P Pending

R Running

/*Get the Details of the Program */

SELECT 'Concurrent request number :' || a.request_id,
       'Oracle session id(SID)    :' || d.SID,
       'Serial Hash value         :' || d.serial#,
       'OS process id (SPID)      :' || c.spid,
       'Phase Code                :' || DECODE (a.phase_code,
                  'P', 'Pending',
                  'R', 'Running',
                  'C', 'Completed'
                 ) "PHASE_CODE",
       'Status Code               :' || DECODE (a.status_code,
                  'H', 'On Hold',
                  'I', 'Normal',
                  'W', 'Paused',
                  'S', 'Suspended',
                  'T', 'Terminating',
                  'D', 'Cancelled',
                  'X', 'Terminated'
                 ) "STATUS_CODE",
       'Actual Start Date        : ' || a.actual_start_date,
       'Actual Complete Date     : ' || a.actual_completion_date,
       'Run Time                 : ' || TRUNC (  (  (actual_completion_date - actual_start_date)
                    / ((1 / 24))* 60)* 60) secs
  FROM applsys.fnd_concurrent_requests a,
       applsys.fnd_concurrent_processes b,
       v$process c,
       v$session d
 WHERE a.controlling_manager = b.concurrent_process_id
   AND c.pid = b.oracle_process_id
   AND c.addr = d.paddr
   AND a.request_id = &req_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/*Get the Details of the Session */

select 'Logon time          :'||to_char(s.logon_time,'mm/dd hh24:mi:ss'),
       'Session id(SID)     :'||s.sid,
       'Status of Session   :'||s.status||'                                             ',
       'Session type        :'||s.type,
       'Session User Name   :'||s.username,
       'OS User             :'||s.osuser,
       'Programme information:'||s.module || ' - ' ||s.program,
       'Process             :'||s.process||'                      ',
       'OS Process id (spid):'||p.spid,
       'SQL Hash Valuee     :'||s.sql_hash_value,
       'Elapsed Time        :'||s.last_call_et       
from v$session s, v$process p
where s.sid = NVL('&trgtsid',s.sid)
and p.spid = NVL(to_number('&trgtspid',999999999),p.spid)
and p.addr = s.paddr;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Even then if the session is existing you can kill the session by issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
or
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Othere way ..

To kill the session via the UNIX operating system,

$ kill -9 spid

Note: Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.