7 Replies Latest reply: Jul 5, 2013 12:53 AM by kvinodhbabu RSS

    How to impdp a specific table excluding rows which are corrupted using impdp

    kvinodhbabu

      Hello,

       

      I need some help with regard to the impdp. I have table which is corrupted and the information of corrupted rows is also available with me now i need to exclude those rows in specific and import the other error free rows.

       

      Can you please let me know what would be the option to use with impdp.

       

      Does the QUERY option work with impdp to exclude rows of that particular table.

       

      Thanks,

      Vinodh

        • 1. Re: How to impdp a specific table excluding rows which are corrupted using impdp
          Srini Chavali-Oracle

          Pl post OS and database versions, Theoretically, the QUERY clause should be able to filter out the rows you want to exclude from the import

           

          Data Pump Import

           

          HTH
          Srini

          • 2. Re: How to impdp a specific table excluding rows which are corrupted using impdp
            kvinodhbabu

            Hello Srini,

             

            Details are below,

             

            Operating System: AIX 7.1

            Database Version: 11g Release 2(11.2.0.3)

             

            Many Thanks,

            Vinodh

            • 3. Re: How to impdp a specific table excluding rows which are corrupted using impdp
              DK2010

              Hi

              Does the QUERY option work with impdp to exclude rows of that particular table.

              Yes, it will Work

              • 4. Re: How to impdp a specific table excluding rows which are corrupted using impdp
                Dean Gagne-Oracle

                The query parameter will work.  You will have to develop a query that will remove the rows that you determined are corrupted.  If it is easier to run multiple queries, you could import the table as is into a test schema, then work on it with any means that you want, then re-export the fixed up table and then import it into your final target destination.  You could use the remap_schema to import the table into the different schema.

                 

                Hope this helps.

                 

                Dean

                • 5. Re: How to impdp a specific table excluding rows which are corrupted using impdp
                  kvinodhbabu

                  but import is running for  more than one day but the size of table is huge around 600GB.

                   

                  Below you can find the parfile output. How to check if this process is still running. I have used dba_datapump_jobs but no use.

                   

                  also truss output says thread is sleeping. Your inputs on this will be much helpful.

                   

                  DIRECTORY=DUMPDIR113

                  DUMPFILE=expdpA_DDAPTE1_20130403_1003_%U.dmp

                  TABLES=A_DDAPTE1.FGBA_JBBB069

                  TABLE_EXISTS_ACTION=APPEND

                  QUERY=A_DDAPTE1.FGBA_JBBB069:"WHERE RECID NOT IN ('600KR7Feb07-5010009050','FTS100913QA-1','FTS100913QA-11','FTS100913QA-14','FTS100913QA-15','FTS100913QA-16'

                  ,'FTS100913QA-17','FTS100913QA-19','FTS100913QA-2','FTS100913QA-25','FTS100913QA-3','FTS100913QA-31','LnLmtTst4Mar06-LMT-1015000','LndPrdMixDec16-4510003850',

                  'LndPrdMixDec16-4510004200','LndPrdMixDec30-4400041826','LndPrdMixJan08-1080005191','LndPrdMixJan19-1020043784','LndPrdMixJan24-1300009271','LndPrdMixJan27-20

                  80004131','LndPrdMixFeb08-2200008537','LndPrdMixFeb12-2061035772','LndPrdMixFeb23-4010020173','LndPrdMixFeb27-4030043858','LnLmtTst4Mar06-LMT-1015000','LndPdM

                  xMr17-1-2040044689','JPMF12309051824404','LndPrdMixMar31-4100030945','LndPrdMixApr26-5020016018','LndPdMxJl09-1-2090030535','JPMJ103090619303826','LndPrdMixSe

                  p03-4070048268','LndPrdMixSep24-4400010477','FTS100913QA-14','FTS100913QA-16','FTS100913QA-2','FTS100913QA-17','FTS100913QA-15','FTS100913QA-25','FTS100913QA-

                  31','FTS100913QA-11','FTS100913QA-19','FTS100913QA-3','FTS100913QA-1','JPMI22909071376392','LndPrdMixDec16-4510003850','LndPrdMixDec16-4510004200','LndPrdMixD

                  ec22-5020009996','LndPdMxJ317-1-2090007173','LndYr2011Jan18-4070033301','LndYr2011Feb02-4400000479','Lndn-11-671BKFeb24-2-1000091345','LndYr2011Mar11-41000182

                  11','RPS09090401918928','LndYr2011Feb26-3050008971','LndYr2011Mar214-4400016890','LndYr2011Apr08-4100023458','FTS09091801399914','LndYr2011May26-5010040755','

                  LndYr2011Jun01-4030034285','Thrd25BulkJun30-5010048461','CobRun0211Jul08-5020030807','600KCsp511Aug09-4030048518','RlTime250k2Sep09-6010025105','CCS0911060114

                  460','3600KR61284NOV24-4030000342','600KR7Feb07-5010009050','FTS09120801346378','600KFWDDATEDMAR09-5010016394','ST10912240129034','MMK0912300369630','600KR701

                  MAY04-4100000188','GBA671Tes2RemDor-3000088468','R21210JUL2012R1-3200014672','CBG1002030116505','R312G10OCT2012R3-3070047935','R312G30OCT2012RR1-3090016437','

                  SOLARIS07DEC2012RR1-3200011526','LINUX23JAN2013RR1-3020015180','LINUX29JAN2013RR1-3050004304','LINUX04FEB2013RT2-3020045579','MMK10032201164879','FTS100324012

                  16453','MMB1003260224532','LINUX14FEB2013RB2-3060003239','LINUX25FEB2013RB1-3100048142','FTS10040802260630','LINUX24MAR2013RB1-4400044062','LINUX23MAY2013RR1-

                  3060001245','LINUX10JUN2013RR3-3030035797','LINUX12JUN2013RR2-3060024808','LINUX14JUN2013RR2-3040046259')"

                  LOGFILE=YYYY069_20130702LOG.log

                  PARALLEL=15

                   

                  Thanks,

                  Vinodh

                  • 6. Re: How to impdp a specific table excluding rows which are corrupted using impdp
                    Richard Harrison .

                    Hi Vinodh,

                    If you look at the first few lines of your export job you will see a job name mentioned.

                     

                    if you run:

                    impdp user/pass attach=job_name

                     

                    It will attach to the running job and give you the current status of it.

                     

                    See what that tells you.

                     

                    Regards,

                    Harry

                     

                    http://dbaharrison.blogspot.com

                    • 7. Re: How to impdp a specific table excluding rows which are corrupted using impdp
                      kvinodhbabu

                      Thanks Harry.

                       

                      Finally the import finished yesterday.

                       

                      Regards,

                      Vinodh