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

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: