Oracle SQL - ashwin-shetty/Documents-Wiki GitHub Wiki

To Check Locks

-- Query which is in waiting state     
SELECT sid, sql_text
FROM v$session s, v$sql q
WHERE sid IN (SELECT sid FROM v$session 
WHERE state IN ('WAITING') AND wait_class != 'Idle' 
AND event='enq: TX - row lock contention' 
AND (q.sql_id = s.sql_id OR q.sql_id = s.prev_sql_id));

-- Blocking session  sid and serial       
SELECT blocking_session, sid,serial#, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

-- Lock Table Name    
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID  

-- Time based query     
select * from employee where  updated date >  sysdate - interval '15' minute