Search This Blog

Wednesday, October 21, 2009

Query to find locked objects in Oracle

Sometimes program takes a little longer time then expected, one of the reason to this could be that the table/object you are trying to manipulate is locked by other program and hence it is waiting for the resource to be released.
Below query can be handy to find the objects that are locked


SELECT c.owner
, c.object_name
, c.object_type
, b.SID
, b.serial#
, b.status
, b.osuser
, b.machine
, b.program
, b.module
, b.action
FROM v$locked_object a
, v$session b
, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
ORDER BY module


If you want to forcefully kill any session then it can be done using

alter system kill session 'sid,serial#'

e.g.
altery system kill session '123,5325'

3 Comments:

venkat said...

Suresh,

The following ques was asked in one interview,

How do u stop inserting records after 50 records are inserted from flat file through SQL Loader?

could u plz answer on this?

Regards,
venkat

Suresh Vaishya said...

There is an option called Load using which you can limit number of rows. I have posted a new post to illustrate an example for same.

sap erp training said...

Wow. Thanks for providing this handy solution to find the objects that are locked. This solution will help in making a faster program and use of the command to forcefully kill any session is a great support when programs reaches in an unresponsive state.

Copyright (c) All rights reserved. Presented by Suresh Vaishya