Sunday, January 10, 2010

Who is locking an Object?

Today I had to update a column of a configuration table. So I executed below query to update that column:

update admuser.cfg_control
set IMP_DIR='/system/cfg/'
where FUNCTION_ID = 121;

After waiting some time there was no result query was still going on. This is unusual, because there are only about 250 records in that table. That means, there was some one who hat locked the table. In order to find out the session locking an object I executed below query:

select *
from v$session
where sid in
( select session_id
from v$locked_object
where object_id in
( select object_id
from dba_objects
where object_name = 'CFG_CONTROL'
and owner = 'ADMUSER')

After that I knew that who was locking that table and I could kill the session or could tell the specific user to logout and then execute the update.
Table v$locked_object has a column locked_mode which describes in number which type of lock is on that table.

locked_mode in table v$locked_object:
0 = none
1 = null
2 = Row- Share
3 = Row- exclusive
4 = Share
5 = Share /Row- exclusive
6 = exclusive

No comments:

Post a Comment