摆渡一下,啥都有了 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 object_id,session_id,locked_mode from v$locked_object;
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;
好像要管理员权限才可以用上面的SQL
select object_id,session_id,locked_mode from v$locked_object;
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语句”,没有要求排除阻塞啊。
oracle有自动解除死锁的功能,在oracle里发生的机率比较少.
有锁很正常啊 看看dba_waiter
--楼主用下面的语句查询下,是不是有记录,如果有记录,就阻塞了,需要kill session 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;
SELECT /*+ rule */ 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'
select o.object_name as 对象名称, o.owner as 所有者, s.sid, s.serial#, p.spid as 系统进程号,case when s.sql_id is null then 'SQL未提交等待' else 'SQL执行等待' end as type, nvl(s.sql_id,s.prev_sql_id) sql_id, q.sql_text 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语句
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语句