How to view Database Lock in Oracle Enterprise Manager

Posted on 2007-08-08
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 34

    Accepted Solution

    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, "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;
    LVL 7

    Assisted Solution

    Click on "Performance"  and then click on "Blocking sessions"

    This will  show you blocking sessions.


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Suggested Solutions

    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…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    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…
    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.

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now