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

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

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    ORA-00054: resource busy and acquire with NOWAIT

    Hello, I need a help and little urgent to meet the deadline of window. Sorry for that.

    I was importing a table streamfilelog. This table has a unique cosntraint and an index. I did not drop the index but disabled the constraint.

    SQL> select index_name,status from user_indexes where index_name like 'STREMFILELOG_UK1';

    INDEX_NAME STATUS
    ------------------------------ --------
    STREMFILELOG_UK1 VALID

    SQL> select constraint_name,status from user_constraints where constraint_name like STREMFILELOG_UK1 ;

    CONSTRAINT_NAME STATUS
    ------------------------------ --------
    STREMFILELOG_UK1 DISABLED


    The import terminates with following warnings. All rows were successfully imported. But I would like to know why am I getting "resource busy warnings"?

    BTW, given the high volume of data in this table, I am running import of this same table in 20 parallel threads.

    "ALTER TABLE "STREMFILELOG" ADD CONSTRAINT "STREMFILELOG_UK1 " UNIQUE ("STREAM_"
    "REF") USING INDEX PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576"
    "00 FREELISTS 5 FREELIST GROUPS 1) TABLESPACE "TS_BILL_D01" LOGGING ENABLE "
    IMP-00003: ORACLE error 54 encountered
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
    Import terminated successfully with warnings.


    IMP: one more observation is when I query the same index, it is in a different TABLESPACE than what is showing here in red above. Any idea?

    A quick response from experts will be sincerely appreciated!!

    Thanks and Regards
    sumit

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ORA-00054 means that other process is locking the target object.

    By the way, if I understood your strategy, after importing the data you do not have to ADD the constraint because constraint already exists - you only have to enable it.

    Try NOVALIDATE option for faster enabling.
    Last edited by PAVB; 05-18-2012 at 05:21 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    Hello PAVB,

    Yes I just need to enable the constraint as I already have disabled. I know that. My question is why import is trying to add the constraint?

    Regards
    sumit

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by sumit View Post
    Yes I just need to enable the constraint as I already have disabled. I know that. My question is why import is trying to add the constraint?
    Oracle's import utility includes the "contraints" parameter which default value is "yes" therefore if you do not specify "contraints=no" you are actually telling Oracle that you want the contraints added.

    If your strategy is to manually handle the contraints after the import gets done then you should include "contraints=no".
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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