Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get the history of database lock

Posted on 2004-08-02
8
Medium Priority
?
9,966 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 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

885 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