ORA-00054: resource busy and acquire with NOWAIT specified

[sandycrab]

Fi Friends...
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
Sandy

[gandolf989]

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.

[grjohnson]

YOu can check this with the following query... i.e. the number of records in a rows in a transaction in the rollback.

SELECT SUBSTR(a.os_user_name,1,8) "OS User"
, SUBSTR(a.oracle_username,1,16) "DB User"
, SUBSTR(b.owner,1,16) "Schema"
, SUBSTR(b.object_name,1,25) "Object Name"
, SUBSTR(b.object_type,1,10) "Type"
, SUBSTR(c.segment_name,1,11) "RBS"
, SUBSTR(d.used_urec,1,12) "# of Records"
FROM v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
WHERE a.object_id = b.object_id
AND a.xidusn = c.segment_id
AND a.xidusn = d.xidusn
AND a.xidslot = d.xidslot
AND d.addr = e.taddr
;

[sandycrab]

Thanks Johnson for your helping hand...It was really a great help…