ORA-00054 when trying to run commands as batch
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-00054 when trying to run commands as batch

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy ORA-00054 when trying to run commands as batch

    Hi list,

    I got no error when trying to run 4 oracle statement manually one after another. But when I tried to put all statements in a .sql file and run that file, I got error message

    ORA-00054: resource busy and acquire with NOWAIT specified

    But there is no other activity on this test server plus I didn't have NOWAIT specified.

    Here are the statements I put in a .sql file:

    drop index REC_DAT_SEARCH;

    create index REC_DAT_SEARCH on I3_RECORDINGDATA (ENTRYTYPE, COMPRESSINGMACHINE, RECORDINGDATE);

    UPDATE I3_RECORDINGDATA SET CALLDIRECTION = 'Inbound'
    wHERE CALLDIRECTION = 'I' AND ENTRYTYPE NOT IN (2, 3);

    UPDATE I3_RECORDINGDATA SET CALLDIRECTION = 'Outbound'
    WHERE CALLDIRECTION = 'O' AND ENTRYTYPE NOT IN (2, 3);


    Thanks for your input!
    Last edited by elaine3839; 06-30-2003 at 05:31 PM.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Session 1

    Code:
    TEST_ABHAY> create index TEST_NO_WAIT on Test_Wait(id);
    
    Index created.
    
    TEST_ABHAY> update TEST_Wait set ID=3 where NAME='Vimal';
    
    1 row updated.
    
    TEST_ABHAY>
    Session 2

    Code:
    TEST:ABHAY> drop index TEST_NO_WAIT;
    drop index TEST_NO_WAIT
               *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified
    Probabaly, some session of your have done DML & uncommited on the table I3_RECORDINGDATA

    Please RTM
    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"

  3. #3
    Join Date
    May 2001
    Posts
    285
    Bingo! You got it! Is there anyway for me to figure out which session actually has the uncommitted DML?

    I did the quick way -- killed all sessions and start doing it as a batch. Of course, that worked :-)

    Originally posted by abhaysk
    Session 1

    Code:
    TEST_ABHAY> create index TEST_NO_WAIT on Test_Wait(id);
    
    Index created.
    
    TEST_ABHAY> update TEST_Wait set ID=3 where NAME='Vimal';
    
    1 row updated.
    
    TEST_ABHAY>
    Session 2

    Code:
    TEST:ABHAY> drop index TEST_NO_WAIT;
    drop index TEST_NO_WAIT
               *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified
    Probabaly, some session of your have done DML & uncommited on the table I3_RECORDINGDATA

    Please RTM

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by elaine3839
    Bingo! You got it! Is there anyway for me to figure out which session actually has the uncommitted DML?
    Check TADDR col in v$session. If not null, then the transaction is either active or done and uncommited.

    v$locked_object also helps you to see which object is locked.
    Last edited by abhaysk; 07-04-2003 at 06:25 AM.
    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"

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