By
Published: 22 Sep 2005
I am new to Oracle and I have a question. If there is a user session that is blocking other sessions and processes from accessing the Oracle database, how can we overcome this problem? Could you please explain with examples, by using a SQL command if you can?
To see what sessions are waiting on a lock to be released, issue the following SQL statement:
SELECT sid,id1 as object_id
FROM v$lock WHERE type='TM' AND request<>0;
If a session is waiting for a lock to be released, you will see the OBJECT_ID of the object the session is waiting for. To see who is holding a lock on that object, issue the following query:
SELECT object_id,owner,object_name,username
FROM v$session s, v$locked_object l, dba_objects o WHERE s.sid=l.session_id AND l.object_id=o.object_id ORDER BY 1;
Look for the object_id in the second query's output to see which session(s) are holding the lock.
Dig Deeper on Oracle database backup and recovery
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading