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