Who locked the table ?

26 10 2009

Anytime curious your query locked any table? Who else is using the same table?

Wanna find the session and kill :P 
SELECT ao.object_name,
decode( vlo.locked_mode,  
0, 'None', /* Mon Lock equivalent */   
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */   
3, 'Row-X (SX)', /* R */  
4, 'Share', /* S */   
5, 'S/Row-X (SSX)', /* C */  
6, 'Exclusive',
vlo.locked_mode) Locked_Mode, /* X */ 
session_id , oracle_username, os_user_name, process 
FROM v$LOCKED_OBJECT vlo, ALL_OBJECTS ao 
WHERE vlo.object_id = ao.object_id 
AND vlo.locked_mode in (3, 5, 6) and ao.object_name like '%<tablename>%'

 Get the session id and kill the session
SELECT* FROM v$session WHERE sid=<>;
Advertisements