Solved

Get the history of database lock

Posted on 2004-08-02
8
7,874 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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

760 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

23 Experts available now in Live!

Get 1:1 Help Now