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.
