<p>--相关表<br /> SELECT * FROM v$lock;<br /> SELECT * FROM v$sqlarea;<br /> SELECT * FROM v$session;<br /> SELECT * FROM v$process;<br /> SELECT * FROM v$locked_object;<br /> SELECT * FROM all_objects;<br /> SELECT * FROM v$session_wait;</p> 

–查看被锁的表 
select b.owner, b.object_name, a.session_id, a.locked_mode
  from v\(locked_object a, dba_objects b<br />  where b.object_id &#61; a.object_id;</p> <p>--查看被锁的表信息<br /> select sess.sid,<br />        sess.serial#,<br />        lo.oracle_username,<br />        lo.os_user_name,<br />        ao.object_name,<br />        lo.locked_mode<br />   from v\)locked_object lo, dba_objects ao, v\(session sess<br />  where ao.object_id &#61; lo.object_id<br />    and lo.session_id &#61; sess.sid;</p> <p>--查看那个用户那个进程照成死锁<br /> select b.username, b.sid, b.serial#, logon_time<br />   from v\)locked_object a, v\(session b<br />  where a.session_id &#61; b.sid<br />  order by b.logon_time;<br />  <br /> --查看连接的进程 <br /> SELECT sid, serial#, username, osuser FROM v\)session;

–查出锁定表的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;
 
–这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
–杀掉进程 sid,serial#

alter system kill session ‘386,25655’;

讯享网

–查询Oracle正在执行的sql语句及执行该语句的用户
select b.sid      oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid       操作系统ID,
       paddr,
       sql_text   正在执行的SQL,
       b.machine  计算机名
  from v\(process a, v\)session b, v\(sqlarea c<br />  where a.addr &#61; b.paddr<br />    and b.sql_hash_value &#61; c.hash_value;</p> <p>-- 检查Oracle是否存在死锁<br /> select sess.sid,<br />        sess.serial#,<br />        lo.oracle_username,<br />        lo.os_user_name,<br />        ao.object_name,<br />        lo.locked_mode<br />   from v\)locked_object lo, dba_objects ao, v\(session sess<br />  where ao.object_id &#61; lo.object_id<br />    and lo.session_id &#61; sess.sid;</p> <p>--检查Oracle死锁的sql<br /> select sql_text<br />   from v\)sql
 where hash_value in
       (select sql_hash_value
          from v\(session<br />          where sid in (select session_id from v\)locked_object));

讯享网

–检查Oracle死锁用户的sql
select b.username, b.serial#, d.id1, a.sql_text
  from v\(lock d, v\)session b, v\(sqltext a<br />  where b.lockwait &#61; d.kaddr<br />    and a.address &#61; b.sql_address<br />    and a.hash_value &#61; b.sql_hash_value;</p> <p>--查询确定具体是谁锁了谁<br /> select s1.username || &#39;&#64;&#39; || s1.machine || &#39; ( SID&#61;&#39; || s1.sid ||<br />        &#39; ) is blocking &#39; || s2.username || &#39;&#64;&#39; || s2.machine || &#39; ( SID&#61;&#39; ||<br />        s2.sid || &#39; ) &#39; AS blocking_status<br />   from v\)lock l1, v\(session s1, v\)lock l2, v\(session s2<br />  where s1.sid &#61; l1.sid<br />    and s2.sid &#61; l2.sid<br />    and l1.BLOCK &#61; 1<br />    and l2.request &gt; 0<br />    and l1.id1 &#61; l2.id1<br />    and l2.id2 &#61; l2.id2;</p> <p>--查询SQL的执行进度<br /> select se.sid,<br />        opname,<br />        trunc(sofar / totalwork * 100, 2) || &#39;%&#39; as pct_work,<br />        elapsed_seconds elapsed,<br />        round(elapsed_seconds * (totalwork - sofar) / sofar) remain_time,<br />        sql_text<br />   from v\)session_longops sl, v\(sqlarea sa, v\)session se
 where sl.sql_hash_value = sa.hash_value
   and sl.sid = se.sid
   and sofar != totalwork
   and sofar &gt; 0
 order by start_time;

–查询Oracle死锁的进程
SELECT s.username,
       l.OBJECT_ID,
       l.SESSION_ID,
       s.SERIAL#,
       l.ORACLE_USERNAME,
       l.OS_USER_NAME,
       l.PROCESS
  FROM V\(LOCKED_OBJECT l, V\)SESSION S
 WHERE l.SESSION_ID = S.SID;