How do I find table Locks?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How do I find table Locks?

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    28

    Question

    I'm new to the DBA world.

    Does anyone have a query that can list wich processes or users have locks on any particular tables??

    Please help.

  2. #2
    Join Date
    Apr 2001
    Posts
    151
    select a.object_id,a.object_name,b.sid,b.serial#,b.username,
    c.os_user_name,c.locked_mode
    from v$session b, v$locked_object c , dba_objects a
    where a.object_id=c.object_id and
    b.sid=c.session_id;
    Elin@trend

  3. #3
    Join Date
    Feb 2001
    Posts
    176

  4. #4
    Join Date
    Feb 2001
    Location
    Montreal
    Posts
    29
    --Se all bloker
    select * from dba_blockers;

    -- Se all waiters
    select * from dba_waiters;

    you most connect sys
    if you have ORA-00942: table or view does not exist
    run catblok.sql (oracle_home\rdbms\admin)
    Hudson

  5. #5
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Try the following script, which shows you the lock and kill info if you like to kill the session holds the locks.

    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
    /

    Dragon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width