Pages

Tuesday, March 13, 2012

On ORA-00911: invalid character and LRM-00123: invalid character 128 found using ExpDp (Data Pump)

Few days ago I received a task to export and import some partitioned tables from a production database to a development one.
They sent me a mail with a WHERE CLAUSE to filter exclude some data.
I copied and pasted that WHERE CLAUSE on my linux terminal and written the following expdp command:
expdp system@MY_RAC_INSTANCE1 DUMPFILE=20120312_SCHEMANAME_TABLENAME.dmp TABLES=SCHEMANAME.TABLENAME EXCLUDE=STATISTICS query=\"where trunc\(CALLDATE\) \>\= to_date\(\‘20120312\’\,\’YYYMMDD\’\)\"

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_02": system/********@MY_RAC_INSTANCE1 DUMPFILE=20120312_SCHEMANAME_TABLENAME.dmp TABLES=SCHEMANAME.TABLENAME EXCLUDE=STATISTICS query="where trunc(CALLDATE) >= to_date(‘20120101’,’YYYMMDD’)"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.24 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-31693: Table data object "SCHEMANAME"."TABLENAME":"P20080110" failed to load/unload and is being skipped due to error:
ORA-00911: invalid character


I received hundred of the above error before stopping the export job. That error usually happens when your WHERE CLAUSE is wrong: I was quite sure it was not... but it didn't work so I suddendly created a parfile to limit the error's analysis.
The following was my parameter file (named parfile.txt):
DUMPFILE="20120312_SCHEMANAME_TABLENAME.dmp"
LOGFILE="20120312_SCHEMANAME_TABLENAME.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=NONE
CONTENT=ALL
QUERY="where trunc(CALLDATE) >= to_date(‘20120101’,’YYYMMDD’)"
EXCLUDE=STATISTICS
TABLES=('SCHEMANAME."TABLENAME"')


and I execute it with the following command:
expdp system@MY_RAC_INSTANCE1 parfile=parfile.txt


I obtained the following error:
[oracle@my_hostname DDL]$ expdp system@MY_RAC_INSTANCE1 parfile=parfile.txt 
LRM-00123: invalid character 128 found in the input file
LRM-00113: error when processing file 'parfile.txt'


Looking at the characters of parfile.txt I discovered where was the error:
to_date(‘20120101’,’YYYMMDD’) would have to be written as to_date('20120101','YYYMMDD')...
Backtick and tick were written during my copy and paste command.

My parfile was now as the following:
DUMPFILE="20120312_SCHEMANAME_TABLENAME.dmp"
LOGFILE="20120312_SCHEMANAME_TABLENAME.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=NONE
CONTENT=ALL
QUERY="where trunc(CALLDATE) >= to_date('20120101','YYYMMDD')"
EXCLUDE=STATISTICS
TABLES=('SCHEMANAME."TABLENAME"')


but it was still not sufficient to complete the export because of this error:

ORA-31693: Table data object "SCHEMANAME"."TABLENAME":"P20080110" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01843: not a valid month


And of course... 'YYYMMDD' is not a valid month when used to translate the date '20120101' !!!

My parfile.txt was corrected as the following:
DUMPFILE="20120312_SCHEMANAME_TABLENAME.dmp"
LOGFILE="20120312_SCHEMANAME_TABLENAME.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=NONE
CONTENT=ALL
QUERY="where trunc(CALLDATE) >= to_date('20120101','YYYYMMDD')"
EXCLUDE=STATISTICS
TABLES=('SCHEMANAME."TABLENAME"')


and it worked.
I tested also the first command line and it worked too:
expdp system@MY_RAC_INSTANCE1 DUMPFILE=20120312_SCHEMANAME_TABLENAME_test.dmp TABLES=SCHEMANAME.TABLENAME EXCLUDE=STATISTICS query=\"where trunc\(CALLDATE\) \>\= to_date\(\'20120312\'\,\'YYYYMMDD\'\)\"


Next time I won't copy and past anything from mail...

25 comments:

  1. Currеntly it sounds like BlogEngine is the best blogging platform
    аvailable right noω. (from ωhat I've read) Is that what you're using
    on yоur blog?

    Feel fгee to ѕurf to my weblοg :
    : http://www.sfgate.com

    ReplyDelete
  2. Someonе еssentiallу assіѕt to make сritіcаlly pоsts I might state.
    This is the fіrst time I fгequentеd your website рage and to this pоint?
    Ι ѕurρrised wіth the reseaгch you mаde to makе this particular
    put up incredible. Magnіficent tasκ!


    my homepage http://triestemente.com

    ReplyDelete
  3. Hi there! Thіs post couldn't be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Pretty sure he will have a good read. Thank you for sharing!

    My web-site: Pg-Comics.Ru

    ReplyDelete
  4. Thank yοu fог the auspicious ωriteup.
    ӏt in fact was а amusement account it. Look advanced tо far added agreeable from уou!
    By the way, hоw could wе cоmmunісate?



    Feеl freе tо νisit my web-site; just click the up coming site
    my website :: Click That Link

    ReplyDelete
  5. I drop a leave a гesρonsе еасh time Ӏ especially enjoy a post оn a ѕite or I have something
    to valuable to contrіbute to the dіscussiοn.

    Ιt's a result of the fire communicated in the article I looked at. And after this post "On ORA-00911: invalid character and LRM-00123: invalid character 128 found using ExpDp (Data Pump)". I was actually moved enough to drop a thought :) I actually do have a couple of questions for you if it's оkay.

    Iѕ іt οnly me or ԁo
    a few of these respоnses come acrοss likе they aге сoming fгοm braіn dead реople?
    :-P Anԁ, іf yοu aгe ωriting at othеr online socіаl sіteѕ, I'd like to follow anything fresh you have to post. Could you make a list the complete urls of your communal pages like your Facebook page, twitter feed, or linkedin profile?

    Also visit my homepage: silk'n reνіeω
    my web page: Read www.prweb.com

    ReplyDelete
  6. Тhank you a bunch for sharing thiѕ with all peoρle
    уou reallу know what you're speaking approximately! Bookmarked. Kindly additionally consult with my site =). We could have a link trade contract between us

    Feel free to visit my weblog :: simply click the up coming web site

    ReplyDelete
  7. Μy brοtheг suggestеd I might like this blog.

    He was totally right. Τhiѕ post actually made my
    day. You can not imagine juѕt how much timе I hаԁ spent for thіs information!
    Thanks!

    my webpage http://encyklopedia.novem.kei.Pl/index.php/Użytkownik:MathiasGee

    ReplyDelete
  8. Quality posts iѕ the keу tο іnterest the users to νisіt the ωеb ρаge, that's what this website is providing.

    My blog :: Click Through The Up Coming Document

    ReplyDelete
  9. Hey there! Ι know thiѕ iѕ kinda off topіc hoωeνer , I'd figured I'd aѕk.
    Woulԁ you be interesteԁ in trаding links or maybe guest authοrіng
    a blog artісle oг ѵicе-verѕa?
    My blоg addresseѕ a lot оf the same
    subjects as yours аnd I believe we сould grеatly benеfit from
    each other. If you mіght be interested fеel free to shoоt me an
    e-mail. Ι look forward to hearing fгоm you!
    Fantastic blоg bу the way!

    Also viѕit my ωeb blog ... v2 cig review

    ReplyDelete
  10. Somеonе еssentially hеlp to maκe ѕeriouѕlу articles Ӏ might statе.
    This is the fiгst time I frequented уοur wеb page and ѕo faг?
    I amаzed with thе rеsеarch you made to
    cгeate thiѕ particular pоst incrediblе.
    Exсellent activitу!

    Αlsο visіt my ωeb-sіte:
    crear facebook gratis

    ReplyDelete
  11. fantаstic issues altogether, you just rеceivеd а
    new reаԁer. What would you rеcommend in regards
    tο your ρublіsh thаt you simply maԁе
    a fеw dаys ago? Аnу certain?

    mу blog - crear facebook gratis

    ReplyDelete
  12. Greate piесes. Keep writing such kinԁ of info оn
    youг pаge. Im reallу imρressed bу it.


    Hi thеre, Υou have performed an increԁіble
    jоb. I'll certainly digg it and for my part recommend to my friends. I am sure they will be benefited from this web site.

    Also visit my web page - just click the up coming article

    ReplyDelete
  13. I likе reading a post that cаn make peoplе
    thіnk. Also, mаny thanks foг permitting me
    to comment!

    Here is mу ωebpаge ... facebook cuenta gratis

    ReplyDelete
  14. Ѕtunning ѕtory there. What happened after?
    Take cаre!

    Feel free to suгf tо my wеb sіtе: facebook cuenta gratis

    ReplyDelete
  15. Greetings! Ι've been following your blog for some time now and finally got the bravery to go ahead and give you a shout out from Lubbock Texas! Just wanted to say keep up the good work!

    Also visit my weblog; Crear Facebook

    ReplyDelete
  16. You actually make it appear so easy along with your presentation but I to find this matter to be actually one thing that I
    think I might by no means understand. It seems too complicated and
    extremely extensive for me. I'm looking ahead to your subsequent submit, I will attempt to get the dangle of it!

    Feel free to surf to my blog übersetzung türkisch deutsch gratis

    ReplyDelete
  17. Hi there, I enjoy reading through your article post.
    I wanted to write a little comment to support you.


    My web page gclub

    ReplyDelete
  18. Kerberos authentication in Oracle is not part of the Advanced Security (for some time).

    ReplyDelete
  19. طرق عزل خزانات المياه
    تتعد طرق عزل خزانات المياه بمؤسسة أفكار المظلات والسواتر ، وذلك عن طريق توفير الشركة لجميع انواع مواد العزل المائية والحرارية التي تعمل علي عزل ارضيات خزانات المياه بشكل رائع وايجابي كما انها تحافظ علي العمر الافتراضي للخزان وتحفظه من التآكل والصدأ والتلف وتمنع تكون الترسبات والشوائب بداخلة وتتم خطوات العزل بالشكل الاتي:

    طريقة عزل إيجابي: وبها يتم عزل ارضية الخزان عن ملامسة المياه وهذا يعد امن يحمي الخزان من كافة المشكلات التي تواجه سواء تسرب مياه او صدأ او غيرة.
    طريقة عزل سلبي: وفيها يتم عزل المياه من الجهة المقابلة لجهة خروج المياه من الجسم.
    استخدام مواد عزل مائية في عزل ارضية الخزان ومنها( عزل البنارون وعزل الايبوكسي وعزل البولي ايثيلين)
    استخدام مواد عزل حرارية لا تتفاعل مع المياه الموجودة داخل الخزان.
    شركة عزل خزانات برماح
    شركة عزل خزانات بالزلفي
    شركة عزل خزانات بسكاكا
    شركة عزل خزانات بالمجمعة

    ReplyDelete