Forum Stats

  • 3,781,314 Users
  • 2,254,500 Discussions
  • 7,879,640 Comments

Discussions

Data Pump import of INDEX showing error

kokori123
kokori123 Member Posts: 1 Green Ribbon

OS: Linux

DB Version: 12.2.0.1

Issue: Database schema refresh using dpexp/imp

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 exist. 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.

Tagged:

Answers

  • Hasitha Amarasiri
    Hasitha Amarasiri Member Posts: 7 Green Ribbon

    Did you drop the user with cascade command.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited Jun 27, 2021 4:35PM

    You excluded indexes but guess what happens when you import table with PK/UK. Import issues ALTER TABLE ADD CONSTRAINT ... PRIMARY KEY/UNIQUE. At this point Oracle checks if matching index exists and if it doesn't (which is your case) it creates one. Now when you do import INCLUDE=INDEX index creating fails. I am not sure why you are excluding indexes, but you need to exclude both indexes and constraints and then import just indexes and constraints.

    SY.

    Hasitha Amarasiri