Oracle: Find locking session and Kill

Usually you will see some query taking a lot of time and sometime never returns. Well, if it returns ever, you nee d to improve the explain plan of the query.
But if it never returns (usually update query) then it could be a deadlock in the system somewhere. To find out that particular session and query, following query will give more fruitful information for it.

I found if useful for debugging few issues in Oracle Configurator when some page does not return on Apply button click. Well, but in general following queries are not specific to oracle applications, you can use it anywhere to find locking sessions and eventually kill them.

SELECT sq.*, ses.*
FROM   v$sql sq, v$session ses
WHERE  sq.sql_id = ses.sql_id
 and ses.sid IN (SELECT sid
            FROM   v$lock
            WHERE  (id1,id2,TYPE) IN (SELECT id1,
                                             id2,
                                             TYPE
                                         FROM   v$lock
                                         WHERE  request > 0))
;

Find "BLOCKING_SESSION", analyze and kill if required.

select * from v$session where sid=310;


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


ALTER SYSTEM KILL SESSION '276,2323' IMMEDIATE;