This discussion is archived
14 Replies Latest reply: May 20, 2009 11:48 PM by OrionNet RSS

Dump import

KSG Explorer
Currently Being Moderated
Hi,

I am getting the following error while importing the dumpfile using impdp (oracle10gR2)

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 "TSOWN"."EBF_TERMINAL_PK" ON "TSOWN"."EBF_TERMINAL" ("RECID"
) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE
NTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P
OOL DEFAULT) TABLESPACE "T24INDEX" PARALLEL 1

ORA-39083: Object type INDEX failed to create with error:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Thanks in advance
KGS
  • 1. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    Hello,

    You have duplicate data and that's why it's failing, import with this option indexes=N and then create your index after you remove duplicates.

    Regards
  • 2. Re: Dump import
    Aman.... Oracle ACE
    Currently Being Moderated
    KSG wrote:
    Hi,

    I am getting the following error while importing the dumpfile using impdp (oracle10gR2)

    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 "TSOWN"."EBF_TERMINAL_PK" ON "TSOWN"."EBF_TERMINAL" ("RECID"
    ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE
    NTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P
    OOL DEFAULT) TABLESPACE "T24INDEX" PARALLEL 1

    ORA-39083: Object type INDEX failed to create with error:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    Well,
    http://lmgtfy.com/?q=ORA-01452

    gave,
    >
    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.>

    HTH
    Aman....
  • 3. Re: Dump import
    KSG Explorer
    Currently Being Moderated
    The import has thousands of tables. It's hard to create index.

    Is there anyway to take indexes while export (expdp) and late importing indexes without fail?

    Thanks
  • 4. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    Hello,

    You have duplicate data and you need to resolved that first in order to create unique indexes on table.

    Regards
  • 5. Re: Dump import
    KSG Explorer
    Currently Being Moderated
    Thanks...

    So I Can import with index=N.

    Pls lemme know how to recreate index to the imported tables?

    Regards,
    Giri
  • 6. Re: Dump import
    Aman.... Oracle ACE
    Currently Being Moderated
    The best way to create them is via import only but I guess you have data in the tables which is voilating it. The other option can be to get the ddls of the indexes from the export file, do the import with INDEX=N and than run the script containing the ddls of index creation.

    HTH
    Aman....
  • 7. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    Giri,

    Before you can create indexes specially unique, you need to resolve duplicate data issue. Then depending upon what you are using (IMPDP) or regular imp? You can use dbms_metadata.get_ddl API to create sql file for indexes and run them on your target database.

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414

    Regards
  • 8. Re: Dump import
    KSG Explorer
    Currently Being Moderated
    Hi,

    When I include indexes=N while import, I received the following error

    impdp tsown/tsown directory=mydir dumpfile=after.dmp indexes=n logfile=aft1.log

    Thanks
    KSG
  • 9. Re: Dump import
    699921 Explorer
    Currently Being Moderated
    first and foremost.
    i guess from your question you are using IMPDP utility for import and there are no INDEXES =N parameter for IMPDP.
    you can try EXCLUDE=INDEX...but i giuess there is no clear way i have seen to create INDEX script in IMPDP.


    IMP utility has INDEXES=N parameter though, but your dump file should have been exported with EXP utility.
    in case you can go ahead with IMP utility, there is a well known parameter INDEXFILE=filename.sql which created DDL of all the indexes. try that.
  • 10. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    Hello,

    If you are using datapump then you need to use EXCLUDE option (impdp help=Y).

    EXCLUDE=INDEXES

    Regards
  • 11. Re: Dump import
    699921 Explorer
    Currently Being Moderated
    Orionet...from the very first post the user was mentioning IMPDP.
    and any advice for creating DDL OF indexes in IMPDP..i am looking for some suggestions from epxerts in this forum
  • 12. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    Hello,

    Look for sqlfile option in datapump and always refer to oracle docs for example and more information.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref355

    Regards
  • 13. Re: Dump import
    KSG Explorer
    Currently Being Moderated
    Thanks user01....

    My question is...

    Dump Export has been taken with index.

    Now, I have imported successfully with exclude=indexes option.

    How can I set the indexex to the imported tables? (because I dont have idea that which index was set to the tables while taking the exp)

    Please suggest me....

    Thanks
    KSG
  • 14. Re: Dump import
    OrionNet Guru
    Currently Being Moderated
    KSG,

    Create a sqlfile using impdp (datapump), see my previous post with the link to oracle docs to use this option. You can also use dbms_metadata.get_ddl API to generate script for all the indexes for the schama user

    DBMS_METADATA.get_ddl (APIs)
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414

    Regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points