Urgent!!!How to check if my table is locked??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Urgent!!!How to check if my table is locked??

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Urgent!!!How to check if my table is locked??

    Hey,

    This is urgent. One of my table is locked by something and I can't update records in it, it just hangs up on me but I can insert a new record though. Can you tell me if there is a way to find out if my table is locked? I have used V$locked_object command. I don't know much more. A reply would be appriciated. Thanks.

    Also, how to find what is locking my table?
    Last edited by see_one; 07-13-2004 at 05:11 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    1. start your update
    2. in another window, select * from dba_waiters. This will give you the sid of the blocker.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    dba_waiter does not exist in sql plus.

  4. #4
    Join Date
    Jun 2004
    Posts
    125
    I am using 8i by the way. I restarted the server but can you please explain why dba_waiters object does not exist? I'll appreciate it. Thanks.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    as sys, run ?/rdbms/admin/catblock.sql to create
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You have some concepts problem, what do you mean a view doesnt exist in sqlplus? sqlplus is not the database, it's a tool

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    Re: Urgent!!!How to check if my table is locked??

    Originally posted by see_one
    Hey,

    This is urgent. One of my table is locked by something and I can't update records in it, it just hangs up on me but I can insert a new record though. Can you tell me if there is a way to find out if my table is locked? I have used V$locked_object command. I don't know much more. A reply would be appriciated. Thanks.

    Also, how to find what is locking my table?

    try use the v$LOCKED_OBJECT, it will also show who the blocker is:
    SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",
    o.owner, o.object_name, o.object_type
    FROM v$locked_object l, dba_objects o
    WHERE l.object_id = o.object_id
    ORDER BY o.object_id, 1 desc
    /

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    start RTM - Concepts
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    you can use following query


    SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
    b.object_id,substr(b.object_name,1,40) object_name
    from v$session a, dba_objects b, v$locked_object c
    where a.sid = c.session_id
    and b.object_id = c.object_id

  10. #10
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Don't forget the utllockt script in $ORACLE_HOME/rdbms/admin. Running this gives you a nice tree-like report of who's waiting and who's blocking.

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