lock on the table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: lock on the table

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    lock on the table

    for some reason, I have a lock on one table, what do I need to do to unlock this table( there are no DML on this table at the time)

    thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    select * from dba_waiters;
    select * from dba_blockers;

    (these tables are created by running ?/rdbms/admin/utllockt)
    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
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marist89

    (these tables are created by running ?/rdbms/admin/utllockt)
    I'm sorry, it's ?/rdbms/admin/catblock.sql (although you can still get your information from utllockt, but you would have to look at it and understand what its doing)
    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."

  4. #4
    Join Date
    Sep 2002
    Posts
    411
    jeff,

    thanks for the advise. I did shutdown the instance and restart it backup so there is no lock now on the database. but I would like to know more about your advise. If I an the catblock.sql, and now I can view the dba_waiters and dba_blockers tables, what should I look in here. Sorry I wish I still have the lock to have a better understand but can you explain briefly????
    thanks

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    Jeff or others,

    do you have any advises on this lock tables???

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Kill the session locking the table.
    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"

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    I did kill the session which lock the tables, the session is gone; however, the lock tables still there.

    any thoughts???

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by mike2000
    I did kill the session which lock the tables, the session is gone; however, the lock tables still there.
    I am not sure what you are trying to tell...some thing like this

    You Kill Session X which holds lock on Table Y..after you do
    you mean theres no entry in V$Locked_Object on table Y & still you feel theres lock on the table?
    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
    Sep 2002
    Posts
    411
    I ran this script and yes there are two tables still locked.

    SELECT SUBSTR(B.OWNER,1,8) "Owner",
    B.OBJECT_TYPE,
    SUBSTR(B.OBJECT_NAME,1,18) "Object_name" ,
    DECODE(A.LOCKED_MODE,0,'None' ,1,'Null' ,
    2,'Row-S',3,'Row-X' ,
    4,'Share',5,'S/Row-X',
    6,'Exclusive') "Locked_Mode",
    A.SESSION_ID "Sess_ID",
    SUBSTR(A.ORACLE_USERNAME,1,10) "User_name",
    A.OS_USER_NAME "OS_User",
    to_char(c.logon_time,'YYYY/MM/DD HH24:MI:SS') "Logon_Time"
    FROM V$LOCKED_OBJECT A,DBA_OBJECTS B,v$session c
    WHERE A.OBJECT_ID=B.OBJECT_ID
    and a.session_id=c.sid
    ORDER BY B.OWNER,B.OBJECT_TYPE,B.OBJECT_NAME;

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Mike :

    The session indeed is there even after you killed...
    I mean there might have been huge Transaction going on by the session.
    And even if you have Killed it would have been marked for KILL..
    coz it has to rollback the TRANSACTION, until then you will see the locks.
    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"

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