Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

 Finding oracle locked objects

Oracle Database Tips by Donald BurlesonMarch 9, 2015

Question:  How can I find all locked objects in Oracle?  I need a script to identify all locked objects.

Answer:  Oracle provides several method for finding locked objects:

The Oracle data dictionary views can be quite complex and difficult to understand. Hence, many Oracle professionals have to be able to use pre-written scripts to allow us to be able to quickly identify important conditions with the Oracle database.

The following script is commonly used by Oracle professionals who need to quickly find out all database objects that are locked within their system. As we may know, Oracle sets locks in order to manage concurrent updates and ensure that the database maintains its internal integrity.

Most Oracle professionals use of the v$locked_object view in order to gather information about objects that are locked within the Oracle database. The v$locked_object view can also be joined into the v$session view in order to gather session level information (SID, PID, status, machine) , and also joined into the dba_objects view in order to get the owner, the object name, and the type of objects that is currently being locked within the database.

This script can detect locked objects by querying v$locked_object and v$lock:

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;

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
  see code depot for full locking script
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

Here is a sample execution of this script and we can see that SID number 11 is holding a lock on the FND_CONCURRENT_REQUESTS table.

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE SID SERIAL# STATUS OSUSER
------------------ ---------- ---------- -------- ------------------------------
MACHINE
----------------------------------------------------------------
APPLSYS
FND_CONCURRENT_REQUESTS
TABLE 11 29 INACTIVE applmgr
corp-hp1

Oracle has several views for showing lock status, some of which show the username:
  • DBA_BLOCKERS - Shows non-waiting sessions holding locks being waited-on
  • DBA_DDL_LOCKS - Shows all DDL locks held or being requested
  • DBA_DML_LOCKS - Shows all DML locks held or being requested
  • DBA_LOCK_INTERNAL - Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
  • DBA_LOCKS - Shows all locks or latches held or being requested
  • DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks
The DBA_LOCK_INTERNAL view is best to show locks for a specific user, and you can specify the query in the form:
SELECT
NVL(b.username,'SYS') username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
where . . .
Here is a script by Laurent Baylac to show locks in Oracle 10g:

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

SET PAGESIZE 14

-- Search for locked objects
-- To be executed under the SYSTEM account
-- Compatible with Oracle10.1.x and higher
 
select
distinct to_name object_locked
from
v$object_dependency
where
to_address in
(
select /*+ ordered */
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
)
/

The majority of locking issues within Oracle are the result of application design within database applications. One root cause of such lock contention problems lies in a basic misunderstanding of the Oracle locking model. Developers often assume incorrectly that database locking is the same across different platforms.

For instance, a new Oracle developer who is used to writing database applications in Microsoft SQL Server may use the same design approach with Oracle database applications that he/she used with SQL Server. This causes most of the locking issues. The solution is simple: educate the development staff on how Oracle database locking works.

If a lock related hang scenario is encountered, the following SQL statements are useful to help isolate the waiters and blockers involved with locking problems.

Show all sessions waiting for any lock:

select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';

Show sessions waiting for a TX lock:

select * from v$lock where type='TX' and request>0;

Show sessions holding a TX lock:

select * from v$lock where type='TX' and lmode>0;

Of course, one can also view lock activity from the Oracle Enterprise Manager (OEM) database or Grid control application as mentioned earlier. Next to be covered are some issues regarding lock contention as related to use of interested transaction lists at the Oracle database block level.

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster