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)
Data Pump Imp Exclude Index Error [message #684525] Tue, 22 June 2021 20:26 Go to next message
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.

Re: Data Pump Imp Exclude Index Error [message #684526 is a reply to message #684525] Wed, 23 June 2021 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Data Pump Imp Exclude Index Error [message #684542 is a reply to message #684525] Fri, 25 June 2021 01:10 Go to previous message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

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


Current Time: Mon Nov 29 20:17:00 CST 2021