0

I am running an export from Oracle 11g:

$ expdp system/ELIDED JOB_NAME=exp_BTM2CATS SCHEMAS=BTM2CATS file=btm2cats-%u.dmp DIRECTORY=DP_DIR filesize=1900M CONSISTENT=Y DIRECT=Y

Export: Release 11.2.0.1.0 - Production on Wed Jul 31 22:44:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2013-07-31 22:44:29', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode Parameter: "direct=TRUE" Location: Command Line, ignored.
Legacy Mode Parameter: "file=btm2cats-110.dmp" Location: Command Line, Replaced with: "dumpfile=btm2cats-2.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.

... and getting an error:

...
ORA-31693: Table data object "BTM2CATS"."APM_PACKAGE_VERSIONS" failed to load/unload and is being skipped due to error:
ORA-00922: missing or invalid option
...

All other ORA-00922 errors I see references to are when invoking "CREATE TABLE" or perhaps a related "ALTER". This error does not seem to be appropriate for occurring in the middle of a properly-invoked expdp invocation. Can anyone explain what this error means in this context and what I might do to try and fix it?

tshepang
  • 12,111
  • 21
  • 91
  • 136
mormegil
  • 1,811
  • 1
  • 20
  • 22
  • 1
    Are you using legacy-mode parameters intentionally? Do you get the same errors if you don't trigger legacy mode? – Alex Poole Aug 02 '13 at 06:57
  • 1
    If you have access to Oracle Support, look at note 1063932.1, and bug 9243068. If that is the problem then removing the legacy-mode-inducing parameters might stop it; but it isn't clear if using `flashback_time=systimestamp` instead of `consistent=true` will still cause this. You might need to raise an SR if it does, or patch up from the base release you're using. – Alex Poole Aug 02 '13 at 08:26

1 Answers1

1
  1. Change CONSISTENT=Y to flashback_time=systimestamp.
  2. Remove DIRECT=Y (you can think of expdp as always using direct path, whenever possible).
  3. Change FILE= parameter to DUMPFILE= parameter.

So you wouldn't use legacy mode. See if this resolves ORA-922 issue.

Tagar
  • 13,911
  • 6
  • 95
  • 110
  • Thanks! I "solved" this (but did not find any explanation) in similar fashion a long ago by changing to $ expdp system/ELIDED JOB_NAME=exp_BTM2CATS SCHEMAS=BTM2CATS DUMPFILE=btm2cats-%u.dmp PARALLEL=4 ESTIMATE=statistics EXCLUDE=table_statistics METRICS=Y DIRECTORY=DP_DIR filesize=1900M – mormegil Jan 13 '15 at 22:27