Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 390 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
How to see lock on table and query?

Rafi (Oracle DBA)
Member Posts: 1,305 Silver Badge
Best Answer
-
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
Answers
-
Rafi,
Script to find locks in the database
http://www.dbapool.com/dbscripts/script_33.html
HTH
Girish Sharma -
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 -
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 -
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 -
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. -
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 -
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. -
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 -
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 -
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.
This discussion has been closed.