Problem solve
Get help with specific problems with your technologies, process and projects.
User session blocking other sessions
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?
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.
