Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2.

Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?

share|improve this question
What do you mean by "history details"? What sort of locks are you talking about and are you sure you need to worry about them? – Mat Jan 12 '14 at 14:30
I mean I want to display things within specific time interval. – Selahattin Jan 12 '14 at 14:56
What "things" are you talking about? – Mat Jan 12 '14 at 14:58
For example : I want to list all locked tables between 02:00:00 PM - 05:00:00 PM for further analysis. – Selahattin Jan 12 '14 at 15:07
Again, what sort of locks are you talking about? Pretty much no ordinary DML will lock tables, and there is no type of lock that prevents queries. It's not clear what you're trying to prevent/achieve. – Mat Jan 12 '14 at 15:10

Following query gives details of all locks.

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
share|improve this answer

You can query the currently locked objects from V$LOCKED_OBJECT.

There is no history for the locks though, logging all the locks would case huge performance overhead and a lot of data to be stored.

The closest that the database has is Active Session history V$ACTIVE_SESSION_HISTORY, DBA_HIST_ACTIVE_SESS_HISTORY (if you have the proper license to use it), where you can view blocking sessions, statements and other information, but not locked tables. Otherwise you can try to query the appropriate views and save the needed data with your own, custom script.

share|improve this answer

The following script can be used in order quickly identify all lock objects within your Oracle system.

   v$locked_object a ,
   v$session b,
   dba_objects c
   b.sid = a.session_id
   a.object_id = c.object_id;

Reference:-Oracle Tips by Burleson Consulting

share|improve this answer

Using the below query you can find out locks on the table.

column oracle_username format a15;
column os_user_name format a15;
column object_name format a37;
column object_type format a37;
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from 
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, 
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

Blocking Locks

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.