Removing Sessions in Killed Status on Unix (文件 ID 274216.1)

PURPOSE
=======
This article explains how to get rid of idle sessions. Even when we kill idle
sessions from the database, they remain in "killed" status and are accounted for 
the total sessions/processes initialization parameter.

PREFACE
=======
Sometimes we find orphan server processes which will never disappear. They are 
listed in v$session. Because of a non-existent user process, the server process 
stays in the server waiting for the user process to be touched once, which will 
never happen. Orphan processes are created after killing them from the database 
side using:

ALTER SYSTEM KILL SESSION 'sid,serial#'; command.

As a result, the session count reaches the sessions parameter and may throw 
an error like ORA-00018.


PROCEDURE
=========
Before killing a session, we can query v$session to get the session details.

SELECT sid, serial#, username, process,machine, terminal, program, 
osuser, logon_time, last_call_et
FROM v$session
WHERE username IS NOT NULL
AND status != 'ACTIVE'
ORDER BY last_call_et
/

The last_call_et column will have the time in seconds since the session has done
some work (SELECT / DML / DDL etc.)

From this, we know how many hours or days a session has been idle.

Before killing the session from the database, we should find the OS process id 
using the following query:

SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/

o   First we can kill the sessions from the database using the sid and serial#

o   If after sometime they stay in the database (v$session.status = 'KILLED'), 
then we have to kill them from OS with:

kill -9 <spid>


NOTE: 
=====

Killing a background process will terminate the instance.
We can cross check the OS process from the OS:

ps -ef|grep <spid> would give something like:

(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
for a server process.

For a background process it would give:
ora_pmon_isc817