I have a table with some 1 million records. Now I'm tring to delete some 300,000 records from that table based on some condition by executing simple SQL query. It couldn't delete for 25 mins...then I killed that session and thought of truncate the table. I opened another session as admin user and try to truncate table but I got following error...
ORA-00054: resource busy and acquire with NOWAIT specified
could anyone tell how to truncate the table...how to free resources??
Thanks in advance
"Greatest Rewards come only with Greatest Commitments!"
When you killed the session Oracle needed to rollback the changes. Which I'm guessing would require a exclusive lock on the table. I have had that before, if you wait long enough then the old transaction will clear. You should think about scheduling the delete off hours. You can even schedule the delete with DBMS_JOB. If the delete caused disk sort then that would explain why it took so long.
Of course if you monitor the session with enterprise manager then you will know exactly what it is doing.