Oracle session process lock

须以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;