4 Replies Latest reply: Sep 3, 2013 1:33 PM by RodSantander RSS

    Error while Import

    AKPT

      Hello,

       

      Oracle:10.2.0.3.0

      OS: Windows 7

      I have two dump files as

      dump14.dmp>> DATA_ONLY

      dump15.dmp>>METADATA_ONLY

       

      I am getting error while doing import in this way-[First import metadata then data]

       

       

      C:\Users\abc>IMPDP system/xyz@TDB  remap_schema=scott:akk1 dumpfile=dump15.DMP

       

      Import: Release 10.2.0.3.0 - Production on Tuesday, 03 September, 2013 0:43:27

       

      Copyright (c) 2003, 2005, Oracle.  All rights reserved.

       

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

      With the Partitioning, OLAP and Data Mining options

      Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

      Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@TDB remap_schema=scott:akk1 dumpfile=

      dump15.DMP

      Processing object type SCHEMA_EXPORT/USER

      Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

      Processing object type SCHEMA_EXPORT/ROLE_GRANT

      Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

      Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

      Processing object type SCHEMA_EXPORT/TABLE/TABLE

      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

      Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

      Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

      ORA-39082: Object type ALTER_FUNCTION:"AKK1"."PAUSE_FOR_SECS" created with compilation warning

      s

      Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

      Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 00:43:30

       

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      C:\Users\abc>IMPDP akk1/xyz@TDB   dumpfile=dump14.DMP

       

      Import: Release 10.2.0.3.0 - Production on Tuesday, 03 September, 2013 0:44:01

       

      Copyright (c) 2003, 2005, Oracle.  All rights reserved.

       

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

      With the Partitioning, OLAP and Data Mining options

      Master table "AKK1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

      Starting "AKK1"."SYS_IMPORT_FULL_01":  akk1/********@TDB dumpfile=dump14.DMP

      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

      ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to

      error:

      ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

      . . imported "SCOTT"."SALGRADE"                          5.726 KB      15 rows

      ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to e

      rror:

      ORA-00001: unique constraint (SCOTT.PK_EMP) violated

      . . imported "SCOTT"."SQL_TRACE_TEST"                    5.351 KB       5 rows

      . . imported "SCOTT"."BONUS"                                 0 KB       0 rows

      Job "AKK1"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 00:44:03

       

      Why am i getting unique constraint violation error.

      -----------------------------------------------------------------------------------------------------------------------------------

      But when i use

       

      C:\Users\abc>IMPDP system/xyz@TDB  remap_schema=scott:akk1 dumpfile=dump14.DMP

      there is no error.

        • 1. Re: Error while Import
          RodSantander

          Hi AKPT,

           

          For the 1st case, impdp compilation errors are very generic, but generaly code related. You have to look for more info into:

          select * from dba_errors where name = 'PAUSE_FOR_SECS';

           

          For the second one, aparently you are trying to insert duplicated values into these tables. Did you truncated the tables before insert ? You can use IMPDP option TABLE_EXISTS_ACTION=TRUNCATE in the command it will automatically truncate the table.

           

          Hope it helps

           

          Regards,

          Rodrigo

          • 2. Re: Error while Import
            AKPT

            Hello Rodrigo,

             

            Thanks for your guidance.But here first i have import the dump file which contains only metadata then what will be the use of truncate.

             

            Kindly help me to understand this point.

             

            Thanks

            • 3. Re: Error while Import
              DK2010

              Hi,

               

              You get the error when you use dump14.dmp>> DATA_ONLY


              IMPDP akk1/xyz@TDB   dumpfile=dump14.DMP

              you are not using remap_schema in import command , so data goes into scott schema itself and throwing error for constrains violation, because data is already there in scott.<tables>.



              HTH

              • 4. Re: Error while Import
                RodSantander

                Hi AKPT,

                 

                I think below is your solution:

                 

                 

                1) IMPDP system/xyz@TDB  remap_schema=scott:akk1 dumpfile=dump15.DMP

                2) IMPDP akk1/xyz@TDB   remap_schema=scott:akk1  dumpfile=dump14.DMP TABLE_EXISTS_ACTION=TRUNCATE CONTENT=DATA_ONLY

                 


                The first command will import all metadata from dumpfile schema SCOTT into AKK1 schema.

                The second one will insert just data content from SCOTT into AKK1 schema truncating all existing tables into AKK1 schema.


                Hope it helps


                Regards,

                Rodrigo