求一查询oracle数据库死锁的SQL语句
谢谢
谢谢
解决方案 »
- Oracle数据库连接,桌面程序正常,但在WCF中就出错
- 看到一种数据库设计的方式,说是目前比较先进的方式,但是我不能理解,请高手解释
- oracle安装问题
- oracle字段递增
- 如何看oracel上的一个存储过程执行了多长时间了(或进度情况)?
- oracle官网asm只有x64架构的,怎么回事啊?
- 安装oracle的企业管理器问题
- oracle数据库的tablespace的初始化参数,是否可以缩减?
- 8888端口是给oracle的那个程序占用了?我要改成其他端口。
- Weblogic 漏洞CVE-2019-2725
- 在plsql developer中如何给表设一个自增主键
- 关于Oracle11G如何处理一次性插入多条数据的问题,向各位求解?
select C.sid,C.serial#,B.object_name,C.username,C.machine,C.terminal,C.program
from v$locked_object A,all_objects B,v$session C
where A.session_id = C.sid and A.object_id = B.object_id;
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 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;
1、先查询出来
select b.sid,b.machine,c.object_name,a.oracle_username,
a.locked_mode,b.osuser,
'alter system kill session '''||b.sid||','||b.serial#||''';'
from v$locked_object a,v$session b,all_objects c
where a.session_id=b.sid and a.object_id=c.object_id;2、用sys用户登录,kill session
楼主问的就是“查询oracle数据库死锁的SQL语句”,没有要求排除阻塞啊。
看看dba_waiter
select b.sid,b.machine,c.object_name,a.oracle_username,
a.locked_mode,b.osuser,
'alter system kill session '''||b.sid||','||b.serial#||''';'
from v$locked_object a,v$session b,all_objects c
where a.session_id=b.sid and a.object_id=c.object_id;
死锁是在告警日志中查看,不是用sql查。sql查的锁不是死锁。5篇回复了。.
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine,
d.SQL_TEXT
from v$locked_object a, v$session b, dba_objects c, v$sqltext d
where b.sid = a.session_id
and a.object_id = c.object_id
and b.SADDR = d.ADDRESS
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
查看死锁只能在alert_SID.log中找ORA-00060错误,网上说的查sql的语句都是查阻塞的。
l.ID1,
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 ;然后通过第一条sql查出的sid 进行查询,得到spid 进行进程查杀select * from v$process p ,v$session s where p.ADDR=s.PADDR and s.SID='SID'
from v$locked_object l , dba_objects o , v$session s , v$process p, v$sqltext q
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and nvl(s.sql_id,s.prev_sql_id) = q.sql_id
order by s.sid, p.spid, q.piece
查询阻塞的SQL语句