3.Oracle锁处理

1. 查看哪些session锁

SQL> 
select  sid , serial#  from v$session where sid in (select sid from v$lock where block = 1);

2. 查看session锁

select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

3. 查看谁锁了谁。

select s1.username||'@'||s1.machine||' ( SID=' || s1.sid || ' ) is blocking '|| 
s2.username  ||'@'|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

v$lock(BLOCK=1)-->SID-->v$session(sid,serial#)-->SQL_ADDRESS-->v$sqltext;  
v$locked_object; 

4.查看该会话什么时间执行了什么程序，导致了该锁

select 'machine:'||se.machine||chr(10)||chr(13)||'User:'||se.schemaname||chr(10)||chr(13)
||'LogonSystem:'||to_char(se.logon_time,'yyyy-mm-dd hh24:mi:ss')||chr(10)||chr(13)
||'Exec Time:'||to_char(se.prev_exec_start,'yyyy-mm-dd hh24:mi:ss')||chr(10)||chr(13)
||'BY Program:'||se.program||chr(10)||chr(13)
||'EXEC SQL:'|| sql.sql_text ||chr(10)||chr(13)
||'sql_id:='||se.prev_sql_id||chr(10)||chr(13)
||'Current Status is:'||se.status  as   "LockInformation"
 from v$session se ,v$sqltext sql 
 where se.PREV_SQL_ID=sql.SQL_ID and se.sid=17 ;