OraFAQ Forum: Server Utilities » DataPump Export ORA-00922: missing or invalid option

Home » RDBMS Server » Server Utilities » DataPump Export ORA-00922: missing or invalid option (Oracle Database 11g Enterprise Edition 11.2.0.1.0 64bit Production Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DataPump Export ORA-00922: missing or invalid option [message #678657]

Wed, 25 December 2019 04:01

LiorI
Messages: 9
Registered: December 2019

Junior Member

DataPump export reported an error on one of the schema tables.

DataPump Export command:
EXPDP.EXE SCHEMA1/SCHEMA1PASS@ORCL DIRECTORY=exp_dir1 DUMPFILE=SCHEMA1.dmp LOGFILE=SCHEMA1.LOG EXCLUDE=GRANT FULL=N REUSE_DUMPFILES=Y FLASHBACK_TIME=SYSTIMESTAMP COMPRESSION=ALL

The log shows an error about one table. All the other objects exported succesfully.
SCHEMA1.LOG error:
ORA-31693: Table data object "SCHEMA1"."TABLE1" failed to load/unload and is
being skipped dua to error
ORA-00922: missing or invalid option

The following day another export was fine, but at the next export that error appeared again and is shown ever since.
Is there a way to find which records or fields caused the problem?
Thanks in advance

Report message to a moderator

Re: DataPump Export ORA-00922: missing or invalid option [message #678659 is a reply to message #678657]

Wed, 25 December 2019 06:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

You did a fine job of obfuscating reality to the point of removing any chance to understand why the error is thrown.

Why is "@ORCL" included in expdp command?
How many different DB Server systems are in your environment?
How many different $ORACLE_HOME directories exist on target DB Server system & what are the Oracle version (to 4 decimal places) for each?

Report message to a moderator

Re: DataPump Export ORA-00922: missing or invalid option [message #678660 is a reply to message #678659]

Wed, 25 December 2019 07:02

LiorI
Messages: 9
Registered: December 2019

Junior Member

Oracle details:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
PL/SQL Release 11.2.0.1.0 - Production.
CORE 11.2.0.1.0 Production.
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production.
NLSRTL Version 11.2.0.1.0 - Production.

'@ORCL' is included because the command may be run from another station.
As far as I know there is only one Oracle home. Its on a local network at a customer site.

Report message to a moderator

Re: DataPump Export ORA-00922: missing or invalid option [message #678661 is a reply to message #678660]

Wed, 25 December 2019 07:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I suspect Oracle version mismatch between expdp version & DB version; but I could be totally wrong.

post SQL & results that shows STATUS of Table data object "SCHEMA1"."TABLE1" in the target database.

post results from SQL below
SELECT COUNT(*) FROM SCHEMA1.TABLE1;

Report message to a moderator

Re: DataPump Export ORA-00922: missing or invalid option [message #678668 is a reply to message #678657]

Thu, 26 December 2019 07:09

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

I don't think the invalid option it referrs to is a command line option of your import, but a table definition option of the reported table.

What version and edition of oracle was the export taken from?
What version and edition of oracle is the import going to?
Show us the full DDL of the reported table ("SCHEMA1"."TABLE1") in the source database.

Report message to a moderator

Previous Topic:	Datapump interview question
Next Topic:	External Table with Tab Delimiter Loads Data Wrong

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 26 19:47:39 CDT 2023