DataPump Import (IMPDP) Returns ORA-54 Resource Busy Errors On Index Build
(Doc ID 1428104.1)
Last updated on JULY 26, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Running a DataPump import job fails to create/alter indexes. You see the following errors in the DataPump log file:
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
ALTER INDEX "<OWNER>"."<INDEX NAME>" NOPARALLEL
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
CREATE INDEX "<OWNER>"."<INDEX NAME>" ON "<OWNER>"."<TABLE NAME>" ("<COLUMN NAME 1>", "<COLUMN NAME 2>") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "<TABLE SPACE NAME>" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
CREATE INDEX "<OWNER>"."<INDEX NAME>" ON "<OWNER>"."<TABLE NAME>" ("<COLUMN NAME 1>", "<COLUMN NAME 2>") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "<TABLE SPACE NAME>" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
ALTER INDEX "<OWNER>"."<INDEX NAME>" NOPARALLEL
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
CREATE INDEX "<OWNER>"."<INDEX NAME>" ON "<OWNER>"."<TABLE NAME>" ("<COLUMN NAME 1>", "<COLUMN NAME 2>") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "<TABLE SPACE NAME>" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Failing sql is:
CREATE INDEX "<OWNER>"."<INDEX NAME>" ON "<OWNER>"."<TABLE NAME>" ("<COLUMN NAME 1>", "<COLUMN NAME 2>") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "<TABLE SPACE NAME>" PARALLEL 1
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |