OraFAQ Forum: Server Utilities » Data Pump Imp Exclude Index Error

Home » RDBMS Server » Server Utilities » Data Pump Imp Exclude Index Error (Oracle Database 12c Enterprise Edition Release 12.2.0.1 - 64bit Production on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data Pump Imp Exclude Index Error [message #684525]

Tue, 22 June 2021 20:26

youngb888
Messages: 1
Registered: June 2021

Junior Member

Greetings, I am performing a schema level import after dropping the user. I excluded the index import from the initial import and initiated a separate import.

1st IMPORT JOB:

Import Schema excluding Index:

Starting "SYS"."SYS_IMPORT_SCHEMA_05": "/******** AS SYSDBA" schemas=information exclude=INDEX directory=DATA_PUMP dumpfile=full_dump.%U.dmp logfile=indormation.log parallel=2

......

.......

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Job "SYS"."SYS_IMPORT_SCHEMA_05" successfully completed at Tue Jun 01 19:22:17 2021 elapsed 0 04:05:49

2nd IMPORT JOB with the error:

Import of Index

Starting "SYS"."SYS_IMPORT_SCHEMA_05": "/******** AS SYSDBA" schemas=information include=INDEX directory=DATA_PUMP dumpfile=full_dump.%U.dmp logfile=indormation2.log parallel=2

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:"INFORMATION"."PK_ROOT_xxx_TBL" already exists

ORA-31684: Object type INDEX:"INFORMATION"."PK_TRNX_FAILED_xxxx_INFO" already exists

ORA-39083: Object type INDEX:"INFORMATION"."AUDITLOG_PUID_PK" failed to create with error:

ORA-01408: such column list already indexed

Failing sql is:

CREATE UNIQUE INDEX "INFORMATION"."AUDITLOG_PUID_PK" ON "INFORMATION"."AUDITLOG" ("PUID") PCTFREE 4 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 "INFORMATION_ILOG" PARALLEL 1

ORA-31684: Object type INDEX:"INFORMATION"."PK_TRANSACTION_xxx" already exists

ORA-31684: Object type INDEX:"INFORMATION"."PK_xxx_TO_TBL" already exists

ORA-31684: Object type INDEX:"INFORMATION"."PK_xxxx_OBJ_TBL" already exists

ORA-31684: Object type INDEX:"INFORMATION"."PK_xxxx_OBJ_TBL" already exists

ORA-31684: Object type INDEX:"INFORMATION"."DATA_xxxx_PIMANFILE_PK" already exists

ORA-31684: Object type INDEX:"INFORMATION"."PK_TRNX_PRINCIPALS" already exists

The import is still running but I keep getting this INDEX already exists. I dropped the user prior to starting the import. Any thought on whether these "already exists" messages can be ignored? I can see the tablespace space growing and imp process running.

Report message to a moderator

Re: Data Pump Imp Exclude Index Error [message #684526 is a reply to message #684525]

Wed, 23 June 2021 00:01

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your first import also imports constraints and primary/unique key constraints are implemented via indexes, so your first import creates the indexes associated to these constraints.

If you want to exclude all indexes from the first import, you also have to exclude constraints and include them in the second import.

Report message to a moderator

Re: Data Pump Imp Exclude Index Error [message #684542 is a reply to message #684525]

Fri, 25 June 2021 01:10

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Any feedback?

Report message to a moderator

Previous Topic:	Issue with sqlldr
Next Topic:	DBCA stalling at datapatch.bat after fixing credentials issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 12:48:25 CDT 2025