Oracle数据库锁表的查询方法以及解锁的方法

2022-09-12 19:00:55

1,锁表语句简单查询方法

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

2,对于上面查询的结果来解锁,如下所示

alter system kill session '382,1125'

3，锁表详细信息查询

select dba_objects.object_name,

 v$locked_object.oracle_username,

v$locked_object.os_user_name,

v$locked_object.locked_mode,v$session.process,v$session.program,

v$session.sid,serial# from v$locked_object,dba_objects,

v$session where v$locked_object.object_id=dba_objects.object_id

 and v$locked_object.session_id=v$session.sid;

4，查询当前session正在执行的sql语句

--查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.

SELECT /*+ ORDERED */

 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')  /* 此处233 为SID*/

 ORDER BY piece ASC;