Oracle Table Lock

-- Check Lock Table

select

c.owner,

c.object_name,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;


Do the following >>


select object_id from dba_objects where object_name='tablename';

select * from v$locked_object where object_id=id number;

Note the "oracle_username" and "session_id".


Or you can query v$access

select sid from v$access where owner='table owner' and object='table
name';

Note the session id number or "sid".

select sid, serial#, command, taddr from v$session where sid=session id
number;

Now you have identified the user and what they are doing.

To terminate the session:

Alter system kill session 'sid, serial#' immediate;

The session should now be killed and the lock SHOULD release.

Rechecking "v$locked_object" will tell you this. If the lock does not
immediately release, there may be a rollback occuring.


To check for rollback:

select used_ublk from v$transaction where ADDR=value from TADDR in
v$session;


select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

select
username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER