[Last Call] Learn how to a build a cloud-first strategyRegister Now


How to view Database Lock in Oracle Enterprise Manager

Posted on 2007-08-08
Medium Priority
Last Modified: 2013-11-05
have any idea how to veiw Oracle Database Lock using Enterprise Manager. Which is the database lock located ?

I using Oracle 10.2.0. Tks
Question by:AXISHK
LVL 35

Accepted Solution

Mark Geerlings earned 1000 total points
ID: 19661887
No I don't know how to get OEM to display database locks, but here is a script you can use in SQL*Plus (or TOAD or any other tool that can execute Oracle SQL statements) to see database locks in Oracle.

1.  This works well in our Oracle10.1 database with the "/*+RULE */" hint.  You may nor may not need that for Oracle10.2.
2. Feel free to change the value "16" in the three "substr" commands to value(s) that work better for your database and/or your screen resolution.
3. You will have to be logged in with DBA priviliges to use this.

select /*+RULE */ s.username, o.name "Locked object", l.sid, s.serial#,
p.spid, 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,16)"OS user",
substr(s.machine,1,16) "Machine", substr(s.terminal,1,16) "Terminal"
from v$process p, sys.obj$ o, v$session s, v$lock l, v$locked_object lo
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
and p.addr = s.paddr
order by l.ctime;

Assisted Solution

gattu007 earned 1000 total points
ID: 19664016
Click on "Performance"  and then click on "Blocking sessions"

This will  show you blocking sessions.


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

829 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