6 Replies Latest reply: Dec 28, 2012 1:42 AM by 774224 RSS

    impdp taking forever to complete!

    rsar001
      Hi There,
      We exported (expdp) a list of tables for an exercise and now we need to import them back.. the import is taking a ridiculous long time... and the buzar thing is that the import did the huge tables in no time and is struggling with the small tables!!!
      . . imported "USER_SCH"."VENTITY_AUD"             2.485 GB 16762666 rows
      . . imported "USER_SCH"."VOTERRES_HIST"           2.056 GB 16884918 rows
      . . imported "USER_SCH"."VOTERMAILING_HIST"       1.852 GB 13354282 rows
      . . imported "USER_SCH"."VTRRES_AUD"                1.795 GB 17434488 rows
      . . imported "USER_SCH"."VENTITY_HIST"        1.791 GB 11163791 rows
      . . imported "USER_SCH"."VPART_AUD"           1.572 GB 19045445 rows
      . . imported "USER_SCH"."VMAIL_AUD"            1.481 GB 13690392 rows
      . . imported "USER_SCH"."INFO_AUD"             1.269 GB 10771317 rows
      . . imported "USER_SCH"."WORKITEM_AUD"                1.075 GB 6848834 rows
      . . imported "USER_SCH"."VLINKS_AUD"              1.115 GB 11767631 rows
      . . imported "USER_SCH"."NAMEORD_AUD"             499.8 MB 7262151 rows
      . . imported "USER_SCH"."VRGDOC_AUD"             416.9 MB 3771186 rows
      . . imported "USER_SCH"."VENTITY"                356.1 MB 3584426 rows
      . . imported "USER_SCH"."INFO"                256.6 MB 3748312 rows
      . . imported "USER_SCH"."VRGDOC"                210.4 MB 3656521 rows
      These small tables though are just taking forever and almost nothing is happening!!
      . . exported "USER_SCH"."VPART"              307.4 MB 13368560 rows
      . . exported "USER_SCH"."VMAILING"               208.5 MB 3584426 rows
      . . exported "USER_SCH"."VTRS"                   179.0 MB 3584426 rows
      . . exported "USER_SCH"."VLINKS"                 155.7 MB 3584426 rows
      . . exported "USER_SCH"."NAMEORD"                104.6 MB 6532916 rows
      There is enough free space on the DB, we dropped the indexes and it's still going very very slow..

      Here is the imp command that we're using (we stopped the import and now doing it one by one):

      impdp DUMPFILE=tyd.dmp logfile=tyd.log tables=VTRS CONTENT=data_only TABLE_EXISTS_ACTION=TRUNCATE EXCLUDE=constraint, ref_constraint, index

      Any ideas how we can speed this up please, we really need to get this done asap..

      Thanks
        • 1. Re: impdp taking forever to complete!
          Srini Chavali-Oracle
          Pl post details of OS and database versions. Pl see if these MOS Docs can help

          453895.1 - Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp)
          780784.1 - Datapump Import ( IMPDP ) Is Slower When Table Exists With TABLE_EXISTS_ACTION=TRUNCATE

          HTH
          Srini
          • 2. Re: impdp taking forever to complete!
            rsar001
            Hi There,
            Database version is 11.1.0.7 x64 OS Windows 2008 Server x64.


            Thanks
            • 3. Re: impdp taking forever to complete!
              rsar001
              We left it running and it took 5 hrs to do 1 table without any indexes that is 104mb in size!!
              C:\oracle\admin\diag\rdbms\ccp\ccp\dpdump>impdp user_sch DUMPFILE=myd.dmp logfile=NAMEORD.log tables=NAMEORD
               CONTENT=data_only TABLE_EXISTS_ACTION=TRUNCATE
              
              Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 25 May, 2010 21:00:51
              
              Copyright (c) 2003, 2007, Oracle.  All rights reserved.
              
              Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
              Master table "user_sch"."SYS_IMPORT_TABLE_14" successfully loaded/unloaded
              Starting "user_sch"."SYS_IMPORT_TABLE_14":  user_sch/******** DUMPFILE=myd.dmp logfile=NAMEORD.log tables=NAMEORD C
              ONTENT=data_only TABLE_EXISTS_ACTION=TRUNCATE
              Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
              . . imported "user_sch"."NAMEORD"                104.6 MB 6532916 rows
              Job "EISV1_SCHEMA"."SYS_IMPORT_TABLE_14" successfully completed at 02:22:43
              Any ideas please?
              • 4. Re: impdp taking forever to complete!
                rsar001
                We found out that the problem was triggers.. we disabled the triggers and the impdp went through in no time.. The small tables had fair amount of triggers on them which just killed the import.

                In short, we found that the best approach is to:

                1. disable all constraints.
                2. create a script to drop and re-create indexes
                3. drop the indexes
                4. disable constraints
                5. impdp CONTENT=DATA_ONLY
                6. run the script to create indexes
                7. enable constraints
                8. enable triggers

                Actually, is the order correct for 6 and 7 above? or should it be the other way around?

                Thanks
                • 5. Re: impdp taking forever to complete!
                  Srini Chavali-Oracle
                  Thanks for the sharing the solution

                  Srini
                  • 6. Re: impdp taking forever to complete!
                    774224
                    Hi

                    According to me the order for step 6 and 7 is correct. Since the indexes can be used to verify foreign key constraints.

                    Kind regards