oracle数据库查看锁表的sql语句整理

关键表

• V$LOCKED_OBJECT ：记录锁信息的表
• v$session ：记录会话信息的表
• v$sql：记录执行sql的表
• dba_objects：用来管理对象（表、库等等对象）

查看锁表的SID

sql1

SELECT USERNAME,SID, LOCKWAIT, STATUS, MACHINE, PROGRAM
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object);

sql2

SELECT sess.SID, sess.SERIAL#, lo.oracle_USERNAME, lo.OS_USER_NAME, lo.LOCKED_MODE, ao.OBJECT_NAME
FROM V$LOCKED_OBJECT lo,
     v$session sess,
     dba_objects ao
WHERE lo.SESSION_ID = sess.SID
  AND ao.OBJECT_ID = lo.OBJECT_ID;

• USERNAME：死锁语句所用的数据库用户；
• SID: session_id
• LOCKWAIT：死锁的状态，如果有内容表示被死锁。
• STATUS：状态，active表示被死锁
• MACHINE：死锁语句所在的机器。
• PROGRAM：产生死锁的语句主要来自哪个应用程序。

查看锁表执行语句

用sid查询单个

SELECT sql_text
FROM v$sql
WHERE hash_value IN
      (SELECT sql_hash_value FROM v$session WHERE sid='XXXXXXXX');

查询所有加锁的sql

SELECT sql_text
FROM v$sql
WHERE hash_value IN
      (SELECT sql_hash_value FROM v$session WHERE sid IN (SELECT session_id FROM v$locked_object));

查询未提交事务的SQL

造成死锁的sql事务都必定是未提交的

SELECT s.SID,
       s.USERNAME,
       s.OSUSER,
       s.PROGRAM,
       TO_CHAR(s.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss')                                             AS LOGON_TIME,
       TO_CHAR(t.START_DATE, 'yyyy-mm-dd hh24:mi:ss')                                             AS START_DATE,
       s.STATUS,
       (SELECT q.SQL_TEXT FROM v$sql q WHERE q.LAST_ACTIVE_TIME = t.START_DATE AND rownum <= 1) AS SQL_TEXT
FROM v$session s,
     v$transaction t
WHERE s.SADDR = t.SES_ADDR;

杀死锁

ALTER SYSTEM KILL SESSION 'sess.sid, sess.serial#';

附：数字越大锁级别越高, 影响的操作越多

1级锁有：Select，有时会在v$locked_object出现。

2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。

3级锁有：Insert, Update, Delete, Lock Row Exclusive

没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

4级锁有：Create Index, Lock Share

locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。

00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.

5级锁有：Lock Share Row Exclusive

具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。

6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

总结 

到此这篇关于oracle数据库查看锁表的sql语句的文章就介绍到这了,更多相关oracle查看锁表sql内容请搜索编程客栈(www.cppcns.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.cppcns.com)！