Oracle的锁表与解锁

注意：以下&全要改成$

查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁：

SELECT s.username,
       decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       o.owner,
       o.object_name,
       o.object_type,
       s.sid,
       s.serial#,
       s.terminal,
       s.machine,
       s.program,
       s.osuser
  FROM V&session s, V&lock l, dba_objects o
 WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT Null

杀锁命令：

alter system kill session 'sid,serial#'；
alter system kill session'50,492';（其中'50,492'为上面查出来的'sid,serial#'）

以下几个为相关表

SELECT * FROM V&lock;
SELECT * FROM V&sqlarea;
SELECT * FROM V&session;
SELECT * FROM V&process ;
SELECT * FROM V&locked_object;
SELECT * FROM all_objects;
SELECT * FROM V&session_wait;

1.查出锁定object的session的信息以及被锁定的object名：

关键字段：locked_mode（锁模式，6为排他锁）‌

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
  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#;

2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句，比上面那段多出sql_text和action：

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.action
  FROM V&sqlarea a, V&session s, V&locked_object l
 WHERE l.session_id = s.sid
   AND s.prev_sql_addr = a.address
 ORDER BY sid, s.serial#;

3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode：

SELECT s.sid,
      s.serial#,
      s.username,
      s.schemaname,
      s.osuser,
      s.process,
      s.machine,
      s.terminal,
      s.logon_time,
      l.type
  FROM V&session s, V&lock l
 WHERE s.sid = l.sid
   AND s.username IS NOT NULL
 ORDER BY sid;