看一下 metalink 上這一篇吧Problem Description
-------------------

If "init.ora" resource_limit = true, and idle_time set in profile.

When idle_time exceeds, the session status becomes 'sniped' in v$session,
but sniped sessions never get cleaned up.

Using 'alter system kill session' to kill the session, session status becomes
'killed' in v$session, but still is never cleaned up.


Solution Description
--------------------

On Unix, and if using a dedicated server, use the following shell script to
kill the shadow process (script has been tested on Solaris, AIX, Tru64 and
HPUX):

#!/bin/sh
tmpfile=/tmp/tmp.$$
sqlplus system/manager <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile
  

Explanation
-----------

According to several bugs as shown in references, it's still expected behaviour
that sniped session may never clean up. The only workaround is to kill the
shadow process.

如果os 是 NT 的話看這篇:

====================================Since Windows NT does not provide a tool to kill individual threads, Oracle ships the ORAKILL utility with RDBMS versions 7.3.3.6 & up. This utility has the same effect of a KILL -9 command for killing a shadow process or background process in UNIX.

ORAKILL is run from the Command Prompt in the following syntax:
C:> orakill <Sid> <Threadid>
where: Sid is the Oracle instance to target
Thread is the thread id of the thread to kill

Threadid can be retrived from the spid column of the V$PROCESS data dictionary view.
You can use the following query to find the session threadid.

select p.spid THREADID, s.osuser, s.program
from v$process p, v$session s
where p.addr=s.paddr;