select BLOCKING_SESSION_STATUS, BLOCKING_SESSION
from v$session;

SQL> col User_name format a10
col owner format a10
col object_name format a20
col object_type format a20
set linesize 200
set pagesize 49000
SQL> SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,DBA_objects o,v$session s
WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC

SQL> oradebug setmypid;     
SQL> oradebug hanganalyze 3;

==================查询长事物
SQL> analyze table t_test2 compute statistics;
Table analyzed.
SQL> show parameter timed_statistics
select opname, target, round(sofar/totalwork*100,2) as progress, time_remaining, elapsed_seconds from v$session_longops where sofar < totalwork;

QL>alter system kill session 'sid,serial#'
或者用系统级别的kill以及orakill

更加v$locked_object和v$lock表，查到session，用方法二杀掉session就可以叻。
=====================================================================

-查询锁
SELECT A.sid,
  b.serial#,
  DECODE(A.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType,
  c.object_name,
  ---b.username,
  ---b.osuser,
  DECODE(a.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') LockMode,
  B.MACHINE,
  D.SPID ,
  b.PROGRAM
FROM v$lock a,
  v$session b,
  all_objects c,
  V$PROCESS D
WHERE a.sid    =b.sid
AND a.type    IN ('TM','TX')
AND c.object_id=a.id1
AND B.PADDR    =D.ADDR
  ---order by username
  
  
       SID    SERIAL# LOCKTYPE                    OBJECT_NAME                    LOCKMODE  MACHINE                                                          SPID
---------- ---------- --------------------------- ------------------------------ --------- ---------------------------------------------------------------- ------------
PROGRAM
------------------------------------------------
       610      42050 DML                         SNAP$                          Row-X     localhost.localdomain                                            5398
sqlplus@localhost.localdomain (TNS V1-V3)
       610      42050 DML                         SNAP_REFOP$                    Row-X     localhost.localdomain                                            5398
sqlplus@localhost.localdomain (TNS V1-V3)
       610      42050 DML                         T_MT_LOG_DETAIL                Row-X     localhost.localdomain                                            5398
sqlplus@localhost.localdomain (TNS V1-V3)

============================================================================
１.查哪个过程被锁
　　查V$DB_OBJECT_CACHE视图:
　　SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';
　　2. 查是哪一个SID,通过SID可知道是哪个SESSION.
　　查V$ACCESS视图:
　　SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';
　　3. 查出SID和SERIAL#
　　查V$SESSION视图:
　　SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'
　　查V$PROCESS视图:
　　SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
　　4. 杀进程
　　(1).先杀ORACLE进程:
　　ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';
　　(2).再杀操作系统进程:
　　KILL -9 刚才查出的SPID
　　或
　　ORAKILL 刚才查出的SID 刚才查出的SPID
　　方法二：
　　经常在oracle的使用过程中碰到这个问题，所以也总结了一点解决方法：）
　　1）查找死锁的进程：
　　sqlplus "/as sysdba"
　　SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
　　2）kill掉这个死锁的进程：
　　alter system kill session ‘sid,serial#’;  （其中sid=l.session_id）
　　3）如果还不能解决，
　　select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
　　其中sid用死锁的sid替换。
　　exit
　　ps -ef|grep spid
　　其中spid是这个进程的进程号，kill掉这个Oracle进程。

================================================================================
用下面语句,查找出对数据库对象锁定的用户会话:
SELECTOBJECT_ID,
SESSION_ID,
ERIAL#,
ORACLE_USERNAME,
       OS_USER_NAME,
S.PROCESS
FROM V$LOCKED_OBJECT A, V$SESSION S
WHEREA.SESSION_ID = S.SID
执行下面语句删除用户会话,从面达到解锁的目的:
alter system kill session'sid,serial#';
=================================================================================


查询oracle 死锁

SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
"MR", "Media Recovery",
"RT", "Redo Thread",
"UN", "USER Name",
"TX", "Transaction",
"TM", "DML'',
"UL", "PL/SQL USER LOCK",
"DX", "Distributed Xaction",
"CF", "Control FILE",
"IS", "Instance State",
"FS", "FILE SET",
"IR", "Instance Recovery",
"ST", "Disk SPACE Transaction",
"TS", "Temp Segment",
"IV", "Library Cache Invalidation",
"LS", "LOG START OR Switch",
"RW", "ROW Wait",
"SQ", "Sequence Number",
"TE", "Extend TABLE",
"TT", "Temp TABLE",
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, "None",
1, "NULL",
2, "ROW-S (SS)",
3, "ROW-X (SX)",
4, "SHARE",
5, "S/ROW-X (SSX)",
6, "EXCLUSIVE",
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, "None",
1, "NULL",
2, "ROW-S (SS)",
3, "ROW-X (SX)",
4, "SHARE",
5, "S/ROW-X (SSX)",
6, "EXCLUSIVE",
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, "NOT Blocking", /**//* Not blocking any other processes */
1, "Blocking", /**//* This lock blocks other processes */
2, "Global", /**//* This lock is global, so we can''t tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> "SYSTEM")
AND (bs.username <> "SYS")
ORDER BY 1;
--------------------------------------------------------------------------------
查询发生死锁的select语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

---------------------------------------------------------
关于数据库死锁的检查方法
一、 数据库死锁的现象
程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。
二、 死锁的原理
当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提
交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，
此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。
三、 死锁的定位方法
通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句。
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
四、 死锁的解决方法
一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。
1）查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
2）kill掉这个死锁的进程：
alter system kill session ‘sid,serial#’; （其中sid=l.session_id）
3）如果还不能解决：
select pro.spid from v$session ses,
v$process pro where ses.sid=XX
and ses.paddr=pro.addr;
其中sid用死锁的sid替换:
exit
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程。



ORACLE里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row- X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row- X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive
??????????????????, ????????????????
1????????Select??????????v$locked_object??????
2????????Select for update,Lock For Update,Lock Row Share
select for update??????????for update????????????????????????????????????????????????????(Row-X)????????????????????????????????????????????????update??delete??select for update??????
3????????Insert, Update, Delete, Lock Row Exclusive
????commit????????????????????????????????, ??????????3????????????????????3????, ??????????????????????????????????
4????????Create Index, Lock Share
locked_mode??2,3,4??????DML(insert,delete,update,select)????, ??DDL(alter,drop ??)??????????ora-00054??????
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5????????Lock Share Row Exclusive
??????????????????????update / delete ... ; ??????????4,5??????
6????????Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
??DBA????, ??????????????????????????????????SQL??????
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
/
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time
/
????????????????????????????????????????????????????????SQL????????????????????????????????
alter system kill session 'sid,serial#';
??????????????????, ????DML??????????????????????????
??????????????????????????????????????????OS???????? $kill process_num ???? $kill -9 process_num????????????????????????????????????????????????????, ??OS??????????????????????
=================================================================
查询oracle 死锁
2008-11-06 04:07:16.0      来源:e800 数据库频道         作者:
关键词：
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
''MR'', ''Media Recovery'',
''RT'', ''Redo Thread'',
''UN'', ''USER Name'',
''TX'', ''Transaction'',
''TM'', ''DML'',
''UL'', ''PL/SQL USER LOCK'',
''DX'', ''Distributed Xaction'',
''CF'', ''Control FILE'',
''IS'', ''Instance State'',
''FS'', ''FILE SET'',
''IR'', ''Instance Recovery'',
''ST'', ''Disk SPACE Transaction'',
''TS'', ''Temp Segment'',
''IV'', ''Library Cache Invalidation'',
''LS'', ''LOG START OR Switch'',
''RW'', ''ROW Wait'',
''SQ'', ''Sequence Number'',
''TE'', ''Extend TABLE'',
''TT'', ''Temp TABLE'',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, ''None'',
1, ''NULL'',
2, ''ROW-S (SS)'',
3, ''ROW-X (SX)'',
4, ''SHARE'',
5, ''S/ROW-X (SSX)'',
6, ''EXCLUSIVE'',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, ''None'',
1, ''NULL'',
2, ''ROW-S (SS)'',
3, ''ROW-X (SX)'',
4, ''SHARE'',
5, ''S/ROW-X (SSX)'',
6, ''EXCLUSIVE'',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, ''NOT Blocking'', /**//* Not blocking any other processes */
1, ''Blocking'', /**//* This lock blocks other processes */
2, ''Global'', /**//* This lock is global, so we can''t tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> ''SYSTEM'')
AND (bs.username <> ''SYS'')
ORDER BY 1;
--------------------------------------------------------------------------------
查询发生死锁的select语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

---------------------------------------------------------
关于数据库死锁的检查方法
一、 数据库死锁的现象
程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。
二、 死锁的原理
当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提
交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，
此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。
三、 死锁的定位方法
通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句。
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
四、 死锁的解决方法
一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。
1）查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
2）kill掉这个死锁的进程：
alter system kill session ‘sid,serial#’; （其中sid=l.session_id）
3）如果还不能解决：
select pro.spid from v$session ses,
v$process pro where ses.sid=645 and
ses.paddr=pro.addr;
其中sid用死锁的sid替换:
exit
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程。


===============================================================================
HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
Doc ID:
Note:122793.1
Subject: HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-OCT-2000
Last Revision Date: 17-JUL-2002
PURPOSE
-------
In some situations it may happen your session is 'hanging' and is awaiting for
a 'Library cache lock'. This document describes how to find the session that
in fact has the lock you are waiting for.

SCOPE & APPLICATION
-------------------
Support analysts, dba's, ..

HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
------------------------------------------------------
Common situations:
* a DML operation that is hanging because the table which is accessed is currently
undergoing changes (ALTER TABLE). This may take quite a long time depending on
the size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).
* The compilation of package will hang on Library Cache Lock and Library Cache Pin
if some users are executing any Procedure/Function defined in the same package.
In the first situation the V$LOCK view will show that the session doing the
'ALTER TABLE' has an exclusive DML enqueue lock on the table object (LMODE=6,
TYPE=TM and ID1 is the OBJECT_ID of the table). The waiting session however does
not show up in V$LOCK yet so in an environment with a lot of concurrent sessions
the V$LOCK information is insufficient to track down the culprit blocking your
operation.
METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------
One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the resources
held & requested by a specific process.
Whilst an operation is hanging, open a new session and launch the following
statement:
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';
Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION with ADDR from V$PROCESS:
SELECT PID FROM V$PROCESS WHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);
The systemstate dump contains a separate section with information for each
process. Open the tracefile and do a search for 'PROCESS pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.
Example output:
PROCESS 8:
----------------------------------------
SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 82 0 4
last post received-location: kslpsr
last process to post me: 5004ff08 1 2
last post sent: 0 0 13
last post sent-location: ksasnd
last process posted by me: 5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 50058ac4
O/S info: user: daemon, term: pts/1, ospid: 15161
OSD pid info: 15161
----------------------------------------
SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 41/LC
O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15
Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.
Example output:
PROCESS 9:
----------------------------------------
SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
<cut> ....
----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]
From the output we can see that the Oracle process with PID 9 has an exclusive
lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
retrieve the sid,user,terminal,program,... for this process. The actual statement
that was launched by this session is also listed in the tracefile (statements and
other library cache objects are preceded by 'name=').

METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------
The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.
You can examine the locks requested (and held) by the waiting session
by looking up the session address (SADDR) in V$SESSION and doing the
following select:
select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'
This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.
You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
If we now match the KGLLKHDL with the handles of other sessions in
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.
SELECT * FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);
If we look a bit further we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
In the same way we can also find all the blocked sessions:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

RELATED DOCUMENTS
-----------------
[NOTE:1020008.6] SCRIPT FULLY DECODED LOCKING SCRIPT
[NOTE:1054939.6] COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK

=============================================
alter session set sql_trace=true;
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc'  trace
from v$process a,v$session b,v$parameter c,v$instance d
where a.addr = b.paddr and b.audsid = userenv('sessionid') and c.name = 'user_dump_dest'

====================

SELECT bs.username "Blocking User",
       bs.username "DB User",
       ws.username "Waiting User",
       bs.SID "SID",
       ws.SID "WSID",
       bs.serial# "Serial#",
       bs.sql_address "address",
       bs.sql_hash_value "Sql hash",
       bs.program "Blocking App",
       ws.program "Waiting App",
       bs.machine "Blocking Machine",
       ws.machine "Waiting Machine",
       bs.osuser "Blocking OS User",
       ws.osuser "Waiting OS User",
       bs.serial# "Serial#",
       ws.serial# "WSerial#",
       wk.TYPE lock_type,
       hk.lmode mode_held,
       wk.request mode_requested,
       TO_CHAR(hk.id1) lock_id1,
       TO_CHAR(hk.id2) lock_id2,
       hk.BLOCK blocking_others
  FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
   AND hk.lmode != 0
   AND hk.lmode != 1
   AND wk.request != 0
   AND wk.TYPE(+) = hk.TYPE
   AND wk.id1(+) = hk.id1
   AND wk.id2(+) = hk.id2
   AND hk.SID = bs.SID(+)
   AND wk.SID = ws.SID(+)
   AND (bs.username IS NOT NULL)
   AND (bs.username <> 'SYSTEM')
   AND (bs.username <> 'SYS')
ORDER BY 1;

xuexile,xieixe