Oracle資料庫鎖表的查詢方法以及解鎖的方法

標籤：style   blog   color   os   io   資料   div   代碼   sp   

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;

Oracle資料庫鎖表的查詢方法以及解鎖的方法