problem in killing session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: problem in killing session

  1. #1
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83

    problem in killing session

    hi,
    I need to truncate a table ..but that table was locked by a user ..i killed the user but it is not killing .. i'm getting this error .

    SQL> truncate table dmn.dmn_plant_material
    2 /
    truncate table dmn.dmn_plant_material
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified


    SQL> alter system kill session '22,20004';
    alter system kill session '22,20004'
    *
    ERROR at line 1:
    ORA-00031: session marked for kill

    but the status of user is KILLED in v$session


    SQL> select sid,serial#,username,osuser,status from v$session where sid = 22;

    SID SERIAL# USERNAME OSUSER STATUS
    ---------- ---------- -------------------- -------------------- --------
    22 20004 BO_DI SYSTEM KILLED


    I then tried truncating and still the same error

    SQL> truncate table dmn.dmn_plant_material
    2 /
    truncate table dmn.dmn_plant_material
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified .


    Plz let me know how to kill this session ..

    Thanx
    sarav
    saravana kumar

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    unless the transaction of the session, that is killed, is rolled back you cannot do anything but just wait.
    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"

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Have you tried looking at all the locks? Don't get me wrong I'm not insulting your intellegence but, try using OEM and using the locks program, maybe someone else has the table locked up.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    hey ..
    i'm almost waiting for 3 hrs but no improvement ..
    even now i tried killing the session ... it tells "session marked to kill' ...

    i even check the v$transaction ..

    SQL> select addr,used_ublk from v$transaction;

    ADDR USED_UBLK
    ---------------- ----------
    00000402A7FBF430 25

    for the past 2 hrs the USED_UBLK is 25 ..it is not moving down or up ..
    saravana kumar

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Have you looked to see that no other processes have that table locked? Utilizing Lock manager?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Sarav,

    Check the waits for that session. You might find the culprit if you find no blocking locks.

    We had a case last week where a session was holding no locks, all other sessions on the instance were shut down, and it was just hung waiting on some system object.

    Restarted the instance and problem went away. (deleting an xsd in 9.2.0.4) - oh, and Oracle Support was clueless after 1 full day of traces, dumps, and an rda.

    Best of luck,
    Last edited by KenEwald; 04-23-2004 at 03:57 AM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    How bout "ORAKILL" ?

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Oct 2003
    Posts
    312
    yeap

    quick way is to go to your os and do "ORAKILL" or "kill -9" for unix

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