lock的总结

----查询各种锁的等待统计信息
select *
  from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time
----tx锁的
select /*+ ordered */ a.sid        
                      blocker_sid,
                      a.username
                      blocker_username,
                      a.serial#,
                      a.logon_time,
                      b.type,    
                      b.lmode mode_held,
                      b.ctime       time_held,
                      c.sid         waiter_sid,     
                      c.request     request_mode,
                      c.ctime       time_waited
   from   v$lock b,
          v$enqueue_lock c,
          v$session a
   where  a.sid = b.sid
     and  b.id1 = c.id1(+)
     and  b.id2 = c.id2(+)
     and  c.type(+) = 'TX'
     and  b.type ='TX'
     and  b.block = 1
order by time_held, time_waited
----根据request 判断
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode,

    request, type

     FROM V$LOCK

   WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

     ORDER BY id1, request
---------tm tx  锁的判断-------
select /*+rule */ lpad('- -',decode(b.block,1,0,4))||s.username username,
b.type, o.owner||'.'||o.object_name object_name,s.sid,s.serial#,decode(b.request,0,'blocked','waiting') status
from dba_objects o,v$session s,v$lock v,v$lock b
where v.id1=o.object_id and v.sid=s.sid
  and v.sid=b.sid and (b.BLOCK=1 or b.REQUEST>0)
  and v.type='TM'
 order by b.id2,v.id1,username desc;
-----------查锁的类型
SELECT chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "Lock",
to_char( bitand(p1, 65535) ) "Mode"
FROM v$session_wait
WHERE event = 'enqueue';

--------------alter system kill session 'sid,serial#';

v$lock;   显示等待被等待的会话
v$enqueue_lock:显示所有等待的会话
v$enqueue_stat:显示各种锁的等待信息
v$locked_object :只包含对象的锁定信息，显示哪些对象被谁锁定被锁定，包括回滚段和会话信息,可以和v$session连接
v$session : row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,blocking_session_status
            blocking_session:阻塞其他会话的sid
            blocking_session_status:状态valid为正在等待
            这几个字段显示等待对象，行，块和，阻塞的会话
            row_wait_obj# = -1 时,这些值无效果。

alter index 是否锁索引 其他用户不能修改数据了

-----------------------------------
dba_blockers
dba_waiters
在$ORACLE_HOME\RDBMS\ADMIN目录下有一个名为utllockt.sql的script，提供了详细的说明，该script输出一个很直观的数据格式。
dba_ddl_locks 视图 在oracle_home/rdms/admin/catblock.sql

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69265/viewspace-448513/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/69265/viewspace-448513/