Solved

Get the history of database lock

Posted on 2004-08-02
8
8,132 Views
Last Modified: 2010-05-18
Hi Experts...

I am facing a problem in oracle database , after continously querying data, data base getting locked.
After some time the lock is getting released.

How can i get the history of database lock that happened in a particular time period ??

Thanx in advance...!!

Rosh :)
0
Comment
Question by:Roshan Davis
8 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 168 total points
ID: 11694354
I don't think there is a history view of database locks as of Oracle10g even.

but you sure can get an snapshot of when the locking  is happening.


use this view to identify the session id that  is locking...

I took it from one of Oracle expert active in this forum ...(shwertner).


set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select     nvl(S.USERNAME,'Internal') username,
     nvl(S.TERMINAL,'None') terminal,
     L.SID||','||S.SERIAL# Kill,
     U1.NAME||'.'||substr(T1.NAME,1,20) tab,
     decode(L.LMODE,1,'No Lock',
          2,'Row Share',
          3,'Row Exclusive',
          4,'Share',
          5,'Share Row Exclusive',
          6,'Exclusive',null) lmode,
     decode(L.REQUEST,1,'No Lock',
          2,'Row Share',
          3,'Row Exclusive',
          4,'Share',
          5,'Share Row Exclusive',
          6,'Exclusive',null) request
from     V$LOCK L,  
     V$SESSION S,
     SYS.USER$ U1,
     SYS.OBJ$ T1
where     L.SID = S.SID  
and     T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5
/
 
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 166 total points
ID: 11694487
I agree, Oracle does not provide you a way to view lock history.  The v$lock view only shows current locks, but each record does include a "ctime" column that indicates the number of seconds this lock has been held.

Here is a script I use to view lock information:
column Mode format a4;
column SID format 999;
column Minutes format 9999999;
select /*+RULE */ s.username, o.name "Locked object", l.sid, l.type, round(l.ctime/60,0) "Minutes",
decode(l.lmode,'1','-','2','RS','3','RX','4','S','5','SRX','6','X') "Mode",
 substr(s.osuser,1,10)"OS user",
substr(machine,1,12) "Machine", substr(terminal,1,10) "Terminal"
from v$locked_object lo, v$lock l, v$session s, sys.obj$ o
where l.sid = lo.session_id
and l.sid > 5
and (l.id2 = lo.xidsqn
 or l.id1 = lo.object_id)
and s.sid = lo.session_id
and o.obj# = lo.object_id;
-- (end of lock script)

I'm curious about the lock problem you have, because continuous querying should not be causing locks.  What other users or processes are active at the same time as the queries?  Other users or processes that are doing updates and/or deletes could be causing locks, but queries and inserts should not.
0
 
LVL 19

Expert Comment

by:grant300
ID: 11694592
Queries can create locks depending on the transaction isolation level.  If you avoid Committed Reads you won't be creating locks for each query.  One way to test this is to commit after each query and see if you still accumulate locks.
0
 
LVL 11

Expert Comment

by:vc01778
ID: 11696965
To grant300


In Oracle,  queries *never* take any locks (except select for update of course but that's a different story).

VC
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 166 total points
ID: 11701736
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now