oracle运维07查看锁资源情况

1. 查看锁表情况；

SELECT /*+ RULE */
 Ls.Osuser Os_User_Name,
 Ls.Username User_Name,
 Decode(Ls.TYPE,
        'RW',
        'Row wait enqueue lock',
        'TM',
        'DML enqueue lock',
        'TX',
        'Transaction enqueue lock',
        'UL',
        'User supplied lock') Lock_Type,
 o.Object_Name OBJECT,
 Decode(Ls.Lmode,
        1,
        NULL,
        2,
        'Row Share',
        3,
        'Row Exclusive',
        4,
        'Share',
        5,
        'Share Row Exclusive',
        6,
        'Exclusive',
        NULL) Lock_Mode,
 o.Owner,
 Ls.Sid,
 Ls.Serial# Serial_Num,
 Ls.Id1,
 Ls.Id2
  FROM Sys.Dba_Objects o,
       (SELECT s.Osuser,
               s.Username,
               l.TYPE,
               l.Lmode,
               s.Sid,
               s.Serial#,
               l.Id1,
               l.Id2
          FROM V$session s, V$lock l
         WHERE s.Sid = l.Sid) Ls
 WHERE o.Object_Id = Ls.Id1
   AND o.Owner <> 'SYS'
 ORDER BY o.Owner, o.Object_Name;

2. 查看锁表、主机、SID/SERIAL

select l.session_id sid,
      s.serial#,
      l.locked_mode,
      l.oracle_username,
      l.os_user_name,
      s.machine,
      s.terminal,
      o.object_name,
      s.logon_time,
       'alter system kill session'''||l.session_id||','||s.serial#||''';' kill 会话
 from v$locked_object l, all_objects o, v$session s
where l.object_id = o.object_id
  and l.session_id = s.sid
       order by sid, s.serial#;

3. 查看导至锁表的 sql 语句（汇总查询）；

select l.session_id sid,
      s.serial#,
      l.locked_mode,
      l.oracle_username,
      s.user#,
      l.os_user_name,
      s.machine,
      s.terminal,
      a.sql_text,
      a.SQL_FULLTEXT,
       o.object_name
 from v$sqlarea a, v$session s, v$locked_object l,all_objects o
where l.object_id = o.object_id
  and l.session_id = s.sid
  and s.prev_sql_addr = a.address
order by sid, s.serial#;

4.sid 查看 sql 语句；

select b.sql_text
 from v$session a, v$sql b
where a.sid = SID 号 --session_id
         and a.SQL_ADDRESS = b.ADDRESS(+);

5. 删除锁表的会话；

----3028:SID,15898:SERIAL#
alter system kill session '3028,15898';