-
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
-
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.
-
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
-
Originally Posted by sumit
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|