14 Replies Latest reply: May 21, 2009 1:48 AM by OrionNet RSS

    Dump import

    KSG
      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
          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....
            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
              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
                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
                  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....
                    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
                      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
                        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
                          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
                            Hello,

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

                            EXCLUDE=INDEXES

                            Regards
                            • 11. Re: Dump import
                              699921
                              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
                                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
                                  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
                                    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