须以Oracle 数据库 system 权限账户登录
-查某session 正在执行的sql语句,从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.
SELECT
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = '233')
ORDER BY piece ASC;
--查进程.
select * from v$process ;
--查锁
select * from v$lock;
--查锁定的对象
select * from v$locked_object
--查事务
select * from v$transaction
--查session
select v.* from v$session v where machine='xxx' and username='xxx' and status='INACTIVE' order by last_call_et desc
--查dba_objects对象
select * from dba_objects
where object_id = '14977'
--查锁定的表
select t2.username,t2.sid,t2.serial#,t3.object_name,t2.OSUSER,t2.MACHINE,t2.PROGRAM,t2.COMMAND,t2.LAST_CALL_ET
from v$locked_object t1,v$session t2 ,dba_objects t3
where t1.session_id=t2.sid and t1.object_id = t3.object_id
order by t2.logon_time;