-
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
-
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"
-
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
-
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
-
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
-
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 02:57 AM.
-
I remember when this place was cool.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|