How to generate KILL SESSION and OS kill commands for a session in Oracle

When ALTER SYSTEM KILL SESSION does not actually clean up the session (rare, but happens on stuck server processes), you have to kill the OS process. This script generates all three commands for a given SID: the SQL-level KILL, a ps grep to find the SPID, and the kill -9. Preview and run only what you need.

State-changing when you actually run the generated commands. kill -9 at OS level terminates the process abruptly and can leave open transactions; the SQL-level KILL SESSION IMMEDIATE is safer where it works. Verify the SPID belongs to the intended session before OS-killing.

ACCEPT p_sid NUMBER PROMPT 'Enter SID: '

SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''';' AS alter_kill,
       'ps -ef | grep LOCAL=NO | grep ' || p.spid                        AS find_ps,
       'kill -9 ' || p.spid                                              AS os_kill
FROM   gv$session s,
       gv$process p
WHERE  p.addr(+)    = s.paddr
AND    p.inst_id(+) = s.inst_id
AND    s.sid        = &&p_sid;