Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get the history of database lock

Posted on 2004-08-02
8
Medium Priority
?
9,721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 672 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 664 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 664 total points
ID: 11701736
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

722 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