Kill sessions

Finding spid for killing process
SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID = <sid_number>;


Finding locks
get sid here
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;


Alternatively try this

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

**********************************************************************************************************************************

find serial here using sid
select * from v$session where sid=6;

Kill session here
alter system kill session '6, 10405';

check the status to see if the session is killed.
select * from v$session where sid=6;

If it says kill pending then
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

**********************************************************************************************************************************

Changes in 11g require instance number to kill sessions on rac

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

SQL>  alter system kill session '130,620,1';
 alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

Now, it works:

SQL>  alter system kill session '130,620,@1';

System altered.