DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-00054: resource busy and acquire with NOWAIT specified

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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
    "Greatest Rewards come only with Greatest Commitments!"

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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
    ;
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks Johnson for your helping hand...It was really a great help…
    "Greatest Rewards come only with Greatest Commitments!"

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