34

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?

4
  • I mean I want to display things within specific time interval.
    – Selahattin
    Jan 12 '14 at 14:56
  • 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
  • Generally I want to find locked tables in oracle ?
    – Selahattin
    Jan 12 '14 at 15:18
  • 1
    @Selahattin Are you talking about an application level lock? Oracle does not generally perform table locking due to its serialisation level and design
    – Philᵀᴹ
    Jan 12 '14 at 17:04
48

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
17

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

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

Reference:-Oracle Tips by Burleson Consulting http://www.dba-oracle.com/t_find_oracle_locked_objects.htm

2

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.

2

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

0

If you wish to release the lock on locked object, then kill the corresponding session.

-- Query to Get List of all locked objects
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 ; 
-- and A.OS_USER_NAME = 'mahendar' 

-- Query to Get List of locked sessions        
select SID,SERIAL#,INST_ID from gv$session a  where schemaname = 'SYSTEM';
-- and osuser =  'mahendar';
-- o/p: 314 26513   1

-- Statement to Kill the session [pass values in the same order and append @ for inst_id]
alter system kill session '314,26513,@1';
0

You can check table lock from v$lock and dba_objects view. Below query will give you the lock details.

select a.sid||'|'|| a.serial#||'|'|| a.process
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME=upper('&TABLE_NAME');

QUERY 2:

select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2;

You can use below query which will give you more detail. Table Lock

-1

Select the session id and kill the process which is holding the lock on schema user.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, 
b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c  
where b.sid = a.session_id and a.object_id = c.object_id;

and then

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';
INST_ID        SID    SERIAL# 
---------- ---------- ---------- 
    1        130        620
SQL>  alter system kill session '130,620,1'; 
alter system kill session '130,620,1' 
* 
ERROR at line 1: 
ORA-00026: missing or invalid session ID
Now, it works:
SQL>  alter system kill session '130,620,@1';
System altered.
1
  • Why do we need this answer posted 7 years after the question is posted? Is there additional information that was not already posted in the existing answers?
    – miracle173
    Jan 3 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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