Oracle 锁表查询大全

----------------------------------
--常用：锁表查询相关
----------------------------------
-- 同时可用如下命令来kill掉当前锁表的项：;
--alter system kill session 'sid, serial#';

--如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 
--如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN 
--以下几个为相关表
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;

--以下的SQL语句列出当前数据库建立的会话情况: 
--注意，上例中SID为1到7(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。
select sid, serial#, username, program, machine, status from v$session;

--查看数据库引起锁表的SQL语句：
SELECT A.USERNAME,
       A.MACHINE,
       A.PROGRAM,
       A.SID,
       A.SERIAL#,
       A.STATUS,
       C.PIECE,
       C.SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT C
 WHERE A.SID IN (SELECT DISTINCT T2.SID
                   FROM V$LOCKED_OBJECT T1, V$SESSION T2
                  WHERE T1.SESSION_ID = T2.SID)
   AND A.SQL_ADDRESS = C.ADDRESS(+)
 ORDER BY C.PIECE;

--查看数据库锁的情况必须要有DBA权限，可以使用一下SQL 语句：
SELECT t2.username, t2.sid, t2.serial#, t2.logon_time
  FROM v$locked_object t1, v$session t2
 WHERE t1.session_id = t2.sid
 ORDER BY t2.logon_time;

--查看被锁的表
SELECT P.SPID,
       A.SERIAL#,
       C.OBJECT_NAME,
       B.SESSION_ID,
       B.ORACLE_USERNAME,
       B.OS_USER_NAME
  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C
 WHERE P.ADDR = A.PADDR
   AND A.PROCESS = B.PROCESS
   AND C.OBJECT_ID = B.OBJECT_ID;

------------------------------
--1.查出锁定object的session的信息以及被锁定的object名
------------------------------
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,
       s.PROGRAM,
       a.sql_text,
       a.SQL_ID,
       a.action
  FROM v$sqlarea a, v$session s, v$locked_object l
 WHERE l.session_id = s.sid
   AND s.SQL_ADDRESS = 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;

/*
SELECT A.OWNER, --OBJECT所属用户
       A.OBJECT_NAME, --OBJECT名称（表名）
       B.XIDUSN,
       B.XIDSLOT,
       B.XIDSQN,
       B.SESSION_ID, --锁表用户的session
       B.ORACLE_USERNAME, --锁表用户的Oracle用户名
       B.OS_USER_NAME, --锁表用户的操作系统登陆用户名
       B.PROCESS,
       B.LOCKED_MODE,       
       C.MACHINE, --锁表用户的计算机名称（例如：WORKGROUP\UserName）
       C.STATUS, --锁表状态
       C.SERVER,
       C.SID,
       C.SERIAL#,
       C.PROGRAM --锁表用户所用的数据库管理工具（例如：ob9.exe）
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
 WHERE A.OBJECT_ID = B.OBJECT_ID
   AND B.PROCESS = C.PROCESS(+)
 ORDER BY 1, 2;

SELECT A.USERNAME,       
       A.MACHINE,      
       A.PROGRAM,       
       A.SID,       
       A.SERIAL#,       
       A.STATUS,       
       C.PIECE,       
       C.SQL_TEXT
  FROM V$SESSION A,       
       V$SQLTEXT C
 WHERE A.SID IN (SELECT DISTINCT T2.SID                 
                   FROM V$LOCKED_OBJECT T1,                        
                        V$SESSION T2                 
                  WHERE T1.SESSION_ID = T2.SID)      
   AND A.SQL_ADDRESS = C.ADDRESS(+)
 ORDER BY C.PIECE;

--
--最近遇到这样一个问题，操作一个表，发现被人锁定了，于是查询谁锁定的表，发现多人锁定，安全起见，不能全部kill，于是用一下语句判定，谁锁定，谁等待;
--
SELECT LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       S.SID,
       S.SERIAL#
  FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
 ORDER BY O.OBJECT_ID, XIDUSN DESC;
 */