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...

24 comments:

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Т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

Anonymous said...

Μ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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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


My web page gclub

Unknown said...

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

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

yanmaneee said...

jordan shoes
christian louboutin sale
valentino shoes
jordan shoes
zx flux
louboutin shoes
jordan shoes
adidas nmd
michael kors bags
retro jordans

Mohamed Abdellatif said...

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

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