2 Replies Latest reply: Aug 24, 2010 3:40 PM by 618702 RSS

    full database impdp

    USR0072
      Hi,

      When i am trying to import data using datapump i am facing the below error.

      OS : AIX 5

      Oracle Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit

      Command used fro EXPORT:'

      expdp admin/XXXXs DIRECTORY=DATA_PUMP_DIR dumpfile=XXXX_24_08_2010.DMP LOGFILE=XXX_24_08_2010.LOG schemas=XXXXX CONTENT=ALL


      Command used for IMPORT:

      impdp admin/XXXXs DIRECTORY=DATA_PUMP_DIR dumpfile=XXXX_24_08_2010.DMP LOGFILE=XXX_24_08_2010.LOG schemas=XXXXX



      Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
      ORA-39083: Object type INDEX failed to create with error:
      ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
      Failing sql is:
      CREATE UNIQUE INDEX "XXXX"."UNI_MODULE_DEF__ID" ON "XXXX"."T_MODULE_DEF" ("COMPONENT_ID", "MODULE_NAME") 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) TABLESPACE "USERS" PARALLEL 1
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      ORA-39083: Object type CONSTRAINT failed to create with error:
      ORA-02299: cannot validate (XXXX.UNI_MODULE_DEF__ID) - duplicate keys found
      Failing sql is:
      ALTER TABLE "XXX"."T_MODULE_DEF" ADD CONSTRAINT "UNI_MODULE_DEF__ID" UNIQUE ("COMPONENT_ID", "MODULE_NAME") USING INDEX 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) TABLESPACE "USERS" ENABLE
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"XXXX"."UNI_MODULE_DEF__ID" creation failed

      Please help me to resolve the issue.
      Thanks
      Rangaraj
        • 1. Re: full database impdp
          618702
          Dear Rangaraj,
          ORA-01452:     cannot CREATE UNIQUE INDEX; duplicate keys found
          Cause:     A CREATE UNIQUE INDEX statement specified one or more columns that currently contain duplicate values. All values in the indexed columns must be unique by row to create a UNIQUE INDEX.
          Action:     If the entries need not be unique, remove the keyword UNIQUE from the CREATE INDEX statement, then re-execute the statement. If the entries must be unique, as in a primary key, then remove duplicate values before creating the UNIQUE index. 
          There are duplicate values in the target table that the import accessed to create the unique index. Anyway, i don't understand what you are trying to achieve? Are you trying to import the export to the same schema?

          Regards.

          Ogan
          • 2. Re: full database impdp
            BelMan
            I think you already have some schemas tables data ,you can truncate tables and
            impdp admin/XXXXs DIRECTORY=DATA_PUMP_DIR dumpfile=XXXX_24_08_2010.DMP LOGFILE=XXX_24_08_2010.LOG schemas=XXXXX

            or
            add TABLE_EXISTS_ACTION parameter

            impdp admin/XXXXs DIRECTORY=DATA_PUMP_DIR dumpfile=XXXX_24_08_2010.DMP LOGFILE=XXX_24_08_2010.LOG schemas=XXXXX TABLE_EXISTS_ACTION=append or TABLE_EXISTS_ACTION=truncate or TABLE_EXISTS_ACTION= REPLACE