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)
DataPump Export ORA-00922: missing or invalid option [message #678657] Wed, 25 December 2019 04:01 Go to next message
LiorI
Messages: 2
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
Re: DataPump Export ORA-00922: missing or invalid option [message #678659 is a reply to message #678657] Wed, 25 December 2019 06:48 Go to previous messageGo to next message
BlackSwan
Messages: 26709
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?
Re: DataPump Export ORA-00922: missing or invalid option [message #678660 is a reply to message #678659] Wed, 25 December 2019 07:02 Go to previous messageGo to next message
LiorI
Messages: 2
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.
Re: DataPump Export ORA-00922: missing or invalid option [message #678661 is a reply to message #678660] Wed, 25 December 2019 07:18 Go to previous messageGo to next message
BlackSwan
Messages: 26709
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;
Re: DataPump Export ORA-00922: missing or invalid option [message #678668 is a reply to message #678657] Thu, 26 December 2019 07:09 Go to previous message
EdStevens
Messages: 1209
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.
Previous Topic: Datapump interview question
Next Topic: External Table with Tab Delimiter Loads Data Wrong
Goto Forum:
  


Current Time: Sun Jun 07 04:51:25 CDT 2020