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

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

babyho http://eServ.biz/_private/nikefreerun.html oowjix [url=http://eServ.biz/_private/nikefreerun.html]eServ.biz/_private/nikefreerun.html[/url] qasjmz [url=http://eServ.biz/_private/nikefreerun.html]nike free run[/url] hgrulw [url=http://eServ.biz/_private/nikefreerun.html]nike free run 2[/url] chattp [url=http://eServ.biz/_private/nikefreerun.html]Nike Free 2[/url] wavwld http://eServ.biz/Classes/_vti_cnf/jordansale.html pgkivn [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]eServ.biz/Classes/_vti_cnf/jordansale.html[/url] cqqcvx [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]cheap jordans[/url] ltlqjx [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]cheap jordan shoes[/url] hthoqu [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]air jordans[/url] vciqln http://korfbal.be/beta/Stats/guccishoessu.html aecdkx [url=http://korfbal.be/beta/Stats/guccishoessu.html]korfbal.be/beta/Stats/guccishoessu.html[/url] tfeirn [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci shoes[/url] uzgwgs [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci outlet[/url] krfvlj [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci store[/url] jnylzy

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

Ruslan Dautkhanov said...

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

Anonymous said...

Hi to every body, it's my first go to see of this web site; this website
carries remarkable and genuinely excellent daata for visitors.


Feeel free tto visit my website; m88

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

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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

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

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

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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

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

Venkat Shanthi said...

Very informative error.Thank you author for posting this kind of errors .


http://www.wikitechy.com/fix-error/ORA-00911-invalid-character-xsql-error



Both are really good.
Cheers,
Venkat