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

unless the transaction of the session, that is killed, is rolled back you cannot do anything but just wait.

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.

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 ..

Have you looked to see that no other processes have that table locked? Utilizing Lock manager?

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,

How bout "ORAKILL" ?

MH

yeap

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