--查看 v$lock 表中的锁信息
select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive') lock_mode,
       decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
from v$lock
where sid in (125,19) order by sid;


--查看TX锁阻塞关系，该SQL无法显示等待表锁资源的会话。
select a.sid blocker_sid,a.serial#,a.username as blocker_username,
       b.type,decode(b.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive') lock_mode,
       b.ctime as time_held,c.sid as waiter_sid,
       decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
       c.ctime time_waited,ob.object_name,ob.object_type
from v$lock b,v$enqueue_lock c,v$session a,v$locked_object lo,dba_objects ob
where a.sid = b.sid
  and b.id1 = c.id1(+)
  and b.id2 = c.id2(+)
  and c.type(+) = 'TX'
  and b.block = 1
  and lo.SESSION_ID= a.sid
  and lo.OBJECT_ID=ob.object_id
order by time_held,time_waited;


BLOCKER_SID    SERIAL# BLOCKER_USERNAME     TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED OBJECT_NAME          OBJECT_TYPE
----------- ---------- -------------------- -- ------------------- ---------- ---------- ------------------- ----------- -------------------- -------------------
        125          5 SYS                  TM Share                      236                                            T1                   TABLE
        
--查看表锁等待关系。注意DML语句在没获得表锁的情况下，是无法获得TX锁的。
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type,
       decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;


      SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID OBJECT_NAME          OBJECT_TYPE         REQUEST_MODE        TY
---------- -- ------------------- ---------- ----------- ---------- -------------------- ------------------- ------------------- --
       125 TM EXclusive                  695         223         19 T1                   TABLE               Row Exclusive       TM

 

select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,ob.object_name ,object_type,
       decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type
from v$lock a,v$lock b,v$locked_object lo,dba_objects ob
where a.id1=b.id1
  and a.sid=lo.SESSION_ID
  and lo.object_id=ob.object_id
  and a.type='TM'
  and b.request>0
  and a.block=1;


--查看表锁等待关系及SQL语句
col sql_text format a80
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'block',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=a.sid
  and sql.sql_id=s.PREV_SQL_ID
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1
union
select a.sid,a.type,decode(a.lmode,0,'None',1,'Null',2,'Row Share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'EXclusive')
       lock_mode,a.CTIME time_held,b.CTIME time_waited
       ,b.sid waiter_sid ,'wait',decode(b.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',
       4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,b.type,
       sql.sql_text
from v$lock a,v$lock b,v$session s,v$sqlarea sql
where a.id1=b.id1
  and s.sid=b.sid
  and sql.sql_id=s.sql_id
  and a.type='TM'
  and a.sid!=b.sid
  and a.block=1;
 
 

       SID TY LOCK_MODE            TIME_HELD TIME_WAITED WAITER_SID 'BLOC REQUEST_MODE        TY SQL_TEXT
---------- -- ------------------- ---------- ----------- ---------- ----- ------------------- -- --------------------------------------------------------------------------------
       125 TM EXclusive                  838         367         19 block Row Exclusive       TM lock table lixia.t1 in exclusive mode
       125 TM EXclusive                  838         367         19 wait  Row Exclusive       TM insert into lixia.t1 (object_id,object_name) values(178876,'test1')