笔记：锁查询1

查询会话锁信息，持有与等待

  column event format a30  
  column sess format a20
  set linesize 150
  break on id1 skip 1
select decode(request,0,'Holder:',' Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
      id1, id2, lmode, request, l.type, ctime, s.sql_id, s.event,s.last_call_et
-- ,s.service_name
  from gv$lock l, gv$session s
  where (id1, id2, l.type) in
    (select id1, id2, type from gv$lock where request>0
    )
   and l.sid=s.sid
   and l.inst_id=s.inst_id    
  order by id1, ctime desc, request;

查询会话信息

 select SID,SERIAL#,COMMAND,STATUS,SQL_ID,PREV_SQL_ID from gv$session where inst_id=2 and sid=1157;

查询sql语句

 select inst_id,sql_text from gv$sqltext where sql_id='31vantjnzcydp' order by inst_id,piece;

通过sid查询该会话持有锁的对象

set linesize 300 pagesize 300
col 机器名 for a20
col 终端用户名 for a10
col 登录机器用户名 for a10
col 登录用户 for a10

SELECT l.session_id sid,
       s.serial#,
       l.INST_ID,  
       l.locked_mode 锁模式,  
       l.oracle_username 登录用户,  
       l.os_user_name 登录机器用户名,  
       s.machine 机器名,  
       s.terminal 终端用户名,  
       o.object_name 被锁对象名,
       s.STATUS,  
       s.logon_time 登录数据库时间  
FROM gv$locked_object l, all_objects o, gv$session s  
WHERE l.object_id = o.object_id  
   AND l.session_id = s.sid  
   AND s.sid=??;