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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close