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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle locks Scripts

Oracle Database Tips by Donald Burleson

Oracle provides many areas of locking:

  • Locks and Oracle
  • Lock management and escalation with Oracle
  • Lock management in an Oracle RAC environment
  • Enhancements to locks with Oracle
  • Tips for resolving lock issues with Oracle
  • Avoiding deadlock conditions with Oracle

Oracle locks have been around a long time since the inception of the first major database release with the Oracle database environment.  What is the purpose of a lock within the Oracle database? Locks function as the primary mechanism to provide for data concurrency and data consistency within the database.

It allows for multiple users to access the data simultaneously while providing a consistent view of data including any changes made by each user's transaction and that of other user transactions made to and against the data within Oracle. Furthermore, locks prevent errors in read and write consistency as part of the relational database ACID model. The database ACID model refers to Atomic, Consistency, Isolation, and Durability. To further explain what ACID means in terms of Oracle and other relational database models, the following explanation illustrates.


For each transaction within the Oracle database, all of the units of work for a transaction must either be all or nothing. In other words, the transaction must be completed or else it must be undone or rolled back. Undo and rollback provide these functions with transactions in concert with locking and latching mechanisms.


Every transaction is required to preserve the integrity constraints which function as part of the declared consistency rules within the Oracle database. Database constraints are the business rules that provide for consistency.


This means that multiple transactions cannot interfere with one another at the same time. Results that are performed in flight, i.e. uncommitted transactions, are not visible to other transactions until a commit phase is executed and completed. Locks provide the mechanism for the isolation phase within the ACID model for Oracle database transactions.

For example, if Sally user locks table A with an exclusive lock, then user Bill will not be able to update the rows in that table until Sally has completed her transaction on that table. If locks did not exist within Oracle, there would be many problems with phantom reads and writes. This concurrency control ensures that all transactions within Oracle are executed safely and according to these rules so that no committed transactions are lost while in the event of a rollback undo operation to abort transactions.


Durability is provided for by the Oracle database engine so that completed transactions are maintained and not lost in the future. Oracle protects against lost transactions by use of committed transactions stored within the undo/rollback segments and undo tablespaces within the Oracle database engine.


Oracle locks Script - locked rows for a user

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:
   NVL(b.username,'SYS') username,
   sys.dba_lock_internal a,
   sys.v_$session b
where  . . .
Here is a script by Laurent Baylac to show locks in Oracle 10g:


COLUMN username FORMAT A15
COLUMN logon_time FORMAT A20

SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       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;


-- Search for locked objects
-- To be executed under the SYSTEM account
-- Compatible with Oracle10.1.x and higher
            distinct to_name object_locked
            to_address in
select /*+ ordered */
        w.kgllkhdl address
            dba_kgllock w,
            dba_kgllock h,
            v$session w1,
            v$session h1
            (((h.kgllkmod != 0) and (h.kgllkmod != 1)
            and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
            (((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.

For a complete list of over 600 Oracle scripts, I would recommend the Oracle script location at .

Related Oracle locks articles:
Oracle row locks and row level locking
Oracle Metric user lock
Oracle Locking Strategies
Locks and ORA-00054 error
Oracle Locking scripts
Oracle 10g release 2 locking changes
Oracle passwords - changing, expiring and locking users
Minimizing Table Locks to Optimize Performance
Oracle Row-Level Locks
SGA System Global Area Components and Locking




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.