Here are two simple scripts to identify an object's locks in the database. Whenever a user complains that there's a session locked, I use these scripts to find out if there are object locks.
# To find locks objects in the database 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; # To find locks objects for a specific session (DML Enqueue) select l.*, o.owner object_owner, o.object_Name from sys.all_objects o, v$lock l where l.sid = :sid -- type a specific sid and l.type = 'TM' and o.object_id = l.id1; # If you need to find all sessions in the dataabse you can use my 'Who' script select decode(sum(decode(decode(s.status,'ACTIVE',s.serial#,0),l.serial#,1,0)),0,'No','Yes') "Long", s.sid "Session ID",s.serial# "Serial#",s.status "Status", s.username "Username", RTRIM(s.osuser) "OS User", b.spid "OS Process ID",s.machine "Machine Name", s.program "Program",c.sql_text "SQL text" from v$session s, v$session_longops l,v$process b, (select address,sql_text from v$sqltext where piece=0) c where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address group by s.sid,s.serial#,s.status,s.username,s.osuser,s.machine, s.program,b.spid, b.pid, c.sql_text order by s.sid;
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2002