How to see lock on table and query?

Rafi (Oracle DBA)

Hi All,
How do we see lock on table and query?


Thanks,
Rafi

Girish Sharma

Yes Rafi,

It is working fine at my end. See below:

Opened Session 1 with scott/tiger and:
update emp set ename='xx' where empno=7499;

Opened Session 2 with scott/tiger and:
update emp set ename='xx' where empno=7499;
<<Its lock here>> This session is locked by above one.

Opened Session 3 with sys/pw as sysdba and:

SQL> set serveroutput on
SQL> BEGIN
  2  dbms_output.enable(1000000);
  3  for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
  4  b.object_name object_name, b.object_type object_type
  5  FROM v$locked_object a, dba_objects b
  6  WHERE xidsqn != 0
  7  and b.object_id = a.object_id)
  8  loop
  9  dbms_output.put_line('.');
 10  dbms_output.put_line('Blocking Session : '||do_loop.session_id);
 11  dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
 12  dbms_output.put_line('Object Type : '||do_loop.object_type);
 13  for next_loop in (select sid from v$lock
 14  where id2 = do_loop.xidsqn
 15  and sid != do_loop.session_id)
 16  LOOP
 17  dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
 18  end loop;
 19  end loop;
 20  END;
 21  /
.
Blocking Session : 139
Object (Owner/Name): SCOTT.EMP
Object Type : TABLE
Sessions being blocked : 134

PL/SQL procedure successfully completed.

HTH
Girish Sharma

Girish Sharma

Rafi,
Script to find locks in the database
http://www.dbapool.com/dbscripts/script_33.html

HTH
Girish Sharma

544167

Rafi,

You can check the v$lock table for any lock on the objects. Following query might help you to find the locking session.

SQL> select * from v$lock where block > 0;

Hope this helps.

Regards,
-Praveen.
http://myracle.wordpress.com

753092

Hi,

Use this query to find locks.

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Regards,
Sathish

Vijayaraghavan Krishnan

Hi,

You can find out using below query.

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;

Regards,
Vijayaraghavan K

Rafi (Oracle DBA)

Hi,
There are various types of locks.When I accessing a query I will be using 3 tables so How I should know a lock is there on any of the table.Do I need to check one by one or is there any other?

I know the query for finding locks in Database.I want to know on specific objects or query?


Thanks,
Rafi.

Girish Sharma

Check this:

set serveroutput on
BEGIN
dbms_output.enable(1000000);
for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line('.');
dbms_output.put_line('Blocking Session : '||do_loop.session_id);
dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
dbms_output.put_line('Object Type : '||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
end loop;
end loop;
END;
/

Blocking Session : 10
Object (Owner/Name): SCOTT.EMP
Object Type : TABLE
Sessions being blocked : 11

PL/SQL procedure successfully completed.

HTH
Girish Sharma

Rafi (Oracle DBA)

Hi Girish,
My stage Database does not displaying any result.I want to test this on my local database.How to create a situation where a lock should occur in scott user for emp,dept tables.


Thanks,
Rafi.

Girish Sharma

Yes Rafi,

It is working fine at my end. See below:

Opened Session 1 with scott/tiger and:
update emp set ename='xx' where empno=7499;

Opened Session 2 with scott/tiger and:
update emp set ename='xx' where empno=7499;
<<Its lock here>> This session is locked by above one.

Opened Session 3 with sys/pw as sysdba and:

SQL> set serveroutput on
SQL> BEGIN
  2  dbms_output.enable(1000000);
  3  for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
  4  b.object_name object_name, b.object_type object_type
  5  FROM v$locked_object a, dba_objects b
  6  WHERE xidsqn != 0
  7  and b.object_id = a.object_id)
  8  loop
  9  dbms_output.put_line('.');
 10  dbms_output.put_line('Blocking Session : '||do_loop.session_id);
 11  dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
 12  dbms_output.put_line('Object Type : '||do_loop.object_type);
 13  for next_loop in (select sid from v$lock
 14  where id2 = do_loop.xidsqn
 15  and sid != do_loop.session_id)
 16  LOOP
 17  dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
 18  end loop;
 19  end loop;
 20  END;
 21  /
.
Blocking Session : 139
Object (Owner/Name): SCOTT.EMP
Object Type : TABLE
Sessions being blocked : 134

PL/SQL procedure successfully completed.

HTH
Girish Sharma

Vijayaraghavan Krishnan

HI,

The above query will itself display the object_type and the object_name (i.e. table) and it will display the owner of it.

Apart from what do you need to find out.

Regards,
Vijayaraghavan K

Rafi (Oracle DBA)

Thanks Girish and Vijay.

One last clarification Girish when I use rollback or commit than only this lock will be release or other conditions are also there?



Thanks,
Rafi.