DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: locks on tables

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi all,
    How can you find out what user has a lock on the table. Also, why is it that even when you bounce the database the lock is still held? Any suggestions as to how to remove the lock.

    Thanks,
    Leonard905

    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Use the v$lock to see what are the sessions that are holding the locks.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    Run the following script to findout which users are helding which locks and kill the user session helding the lock.

    select a.object_id,a.object_name,b.sid,b.serial#,b.username,c.os_user_name,c.locked_mode
    from dba_objects a,v$session b,v$locked_object c
    where a.object_id=c.object_id and
    b.sid=c.session_id;

    The follwoing are the locktypes and their lock mode
    0=None,1=Null,2=Row Share,3=Row Exclusive
    4=Share,5=Share Row Exclusive,6=Exclusive


    When u shutdown the database,all the locks held by the users are released as all the sessions are killed.Better to shutdown the database by immediate option and then check again through the query.


    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  4. #4
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Thanks,
    But even with the shutdown immediate option, the lock is still held by the user.


    Leonard905
    leonard905
    leonard905@yahoo.com

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    In your future problem postings please include the OS and DB versions? Without that it becomes at times hard to say where the problem lies. So please update us with your OS and DB versions.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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