Q
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.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close