This discussion is archived
7 Replies Latest reply: Jul 4, 2013 10:53 PM by kvinodhbabu RSS

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

kvinodhbabu Newbie
Currently Being Moderated

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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 . Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thanks Harry.

     

    Finally the import finished yesterday.

     

    Regards,

    Vinodh

Legend

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