Home » RDBMS Server » Server Utilities » IMP-00017: following statement failed with ORACLE error 922 (Oracle 9i, AIX Version 5.3)
IMP-00017: following statement failed with ORACLE error 922 [message #663015] Thu, 18 May 2017 08:09 Go to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
Hi all
I have a problem

I use a IMP command and returned this error IMP-00017: following statement failed with ORACLE error 922.

Local machine Client Oracle 11.2.0.1.0
Server machine Oracle 9.2.0.8.0
Export using a Oracle V09.02.00

I Copy/Paste the log
-------------------------------------
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Testing options
JServer Release 9.2.0.8.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P9 character set (possible charset conversion)
export client uses WE8ISO8859P9 character set (possible charset conversion)
. importing NPDS's objects into NPDS
. importing NPDSAPL's objects into NPDSAPL
. importing NPDSTOSIR's objects into NPDSTOSIR
. importing NPDS's objects into NPDS
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE OR REPLACE "
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
.
.
.
.
About to enable constraints...
Import terminated successfully with warnings.


How to solve this problem?
Thanks
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663016 is a reply to message #663015] Thu, 18 May 2017 08:19 Go to previous messageGo to next message
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


how & where exactly was export initiated?
how & where exactly was import initiated?

I suspect export file is/was from higher version Oracle DB
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663017 is a reply to message #663016] Thu, 18 May 2017 08:32 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
Thank's for welcome.

Both on UNIX Oracle version 9.2.0.8.0 machine.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663018 is a reply to message #663017] Thu, 18 May 2017 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE is your friend, but only when you actually use it


ORA-00922 missing or invalid option
Cause: An invalid option was specified in defining a column or storage clause. The valid option in specifying a column is NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error.

Action: Correct the syntax. Remove the erroneous option or length specification from the column or storage specification.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663019 is a reply to message #663018] Thu, 18 May 2017 08:45 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
How to fix it?
The data has already been exported to a dump compressed file.
Thank you
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663021 is a reply to message #663019] Thu, 18 May 2017 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
below is what you know that we don't know
1) complete & actual exp command that produced the dump file
2) complete & actual imp command that produced the errors
3) What objects existed in target DB prior to start of import

We can't say what was done wrong since you have not showed us what you actually did do

Re: IMP-00017: following statement failed with ORACLE error 922 [message #663022 is a reply to message #663021] Thu, 18 May 2017 09:00 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
thank you so much
I'll give you a better look and I'll write to you.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663023 is a reply to message #663019] Thu, 18 May 2017 09:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
@BS - How on earth does that help? OP can't edit the dmp file.
@SiuLong - use SQLFILE parameter of impdp to generate a file with the DDL impdp would be running. Then run that in so you can see exactly what is causing the error.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663024 is a reply to message #663023] Thu, 18 May 2017 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
cookiemonster wrote on Thu, 18 May 2017 07:02
@BS - How on earth does that help? OP can't edit the dmp file.
@SiuLong - use SQLFILE parameter of impdp to generate a file with the DDL impdp would be running. Then run that in so you can see exactly what is causing the error.
That will be a real challenge to use impdp on V9 database.

What if the target schema existed in target database prior to import?
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663025 is a reply to message #663024] Thu, 18 May 2017 09:25 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
Hi
I'm using a EXP and IMP command, but not expdp and impdp.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663026 is a reply to message #663025] Thu, 18 May 2017 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SiuLong wrote on Thu, 18 May 2017 07:25
Hi
I'm using a EXP and IMP command, but not expdp and impdp.
post proof above is true
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663027 is a reply to message #663026] Thu, 18 May 2017 09:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well imp have an equivalent parameter which you could have found for yourself if you'd looked in the documentation
It's SHOW.

Re: IMP-00017: following statement failed with ORACLE error 922 [message #663028 is a reply to message #663024] Thu, 18 May 2017 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 18 May 2017 15:04
cookiemonster wrote on Thu, 18 May 2017 07:02
@BS - How on earth does that help? OP can't edit the dmp file.
@SiuLong - use SQLFILE parameter of impdp to generate a file with the DDL impdp would be running. Then run that in so you can see exactly what is causing the error.
That will be a real challenge to use impdp on V9 database.

What if the target schema existed in target database prior to import?
What if target schema existed and imp worked? And I'm presuming this isn't production, if it they're in probably in trouble already.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663029 is a reply to message #663027] Thu, 18 May 2017 09:43 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
I verifying a DBA for used a parameters for EXP.
I'm only using a IMP.
These parameters i'm used are:

imp USER/PASSWORD@DB file=FULL_7.dmp BUFFER=1073741824 LOG=LOG_20170518_101235.log FROMUSER=A,B,C,D ROWS=Y STATISTICS=NONE
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663037 is a reply to message #663028] Fri, 19 May 2017 03:26 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
On the machine not have a impdp command.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663297 is a reply to message #663015] Mon, 29 May 2017 03:15 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
It's possible to use this command EXPDB to export and IMP to import or viceversa?
I searched on web, it seems to me that you can not do it.
Thank's
Re: IMP-00017: following statement failed with ORACLE error 922 [message #663298 is a reply to message #663297] Mon, 29 May 2017 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, you can't.

Re: IMP-00017: following statement failed with ORACLE error 922 [message #665445 is a reply to message #663298] Wed, 06 September 2017 04:22 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
I'm trying and i usage this SHOW=Y parameter.
Output is:

"CREATE OR REPLACE "
"CREATE TYPE "NDBIFALLEGATOTOWS_T" TIMESTAMP '2014-04-04:17:53:13' OID '0BFC"
"8ED7968E5278E044000E7F4E7F15' "
" "
" as table of NdbIfAllegatoTOWS"


I't possibly to remove the statement "CREATE OR REPLACE "?
Do you have any other solution?
THX

Re: IMP-00017: following statement failed with ORACLE error 922 [message #665447 is a reply to message #665445] Wed, 06 September 2017 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exact command did you use to generate the export?
What exact command did you use to generate the output in the post above?
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665455 is a reply to message #663015] Wed, 06 September 2017 07:06 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
SiuLong wrote on Thu, 18 May 2017 08:09
Hi all
I have a problem

I use a IMP command and returned this error IMP-00017: following statement failed with ORACLE error 922.

Local machine Client Oracle 11.2.0.1.0
Server machine Oracle 9.2.0.8.0
Export using a Oracle V09.02.00

I Copy/Paste the log
-------------------------------------
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Testing options
JServer Release 9.2.0.8.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

See that last line, above?

And from the documentation:

Invoking Export As SYSDBA

SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA, except in the following situations:

- At the request of Oracle technical support
- When using transportable tablespaces (see Transportable Tablespaces)


Re: IMP-00017: following statement failed with ORACLE error 922 [message #665485 is a reply to message #665455] Thu, 07 September 2017 04:26 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
The export is done by sysdba and is FULL; the import is done with the IMP <USRNAME</<PWD>@<SID> file=oraclePIPEfile parfile=PARfileName

Our need is to import from that file (though it is full and has been done by sysdba) with a non-sysdba user that has the IMP_FULL_DATABASE privilege.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665498 is a reply to message #665485] Thu, 07 September 2017 08:23 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Then I think you are stuck having to import as SYSDBA. Just another reason why it is bad to export as SYS.

You say the server and export at 9.2, but what is the version of the source database and destination database? Why mention that the client is 11.2?
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665511 is a reply to message #665498] Fri, 08 September 2017 02:17 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
Source and destination as the same Oracle 9.2.0.8.0
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665518 is a reply to message #665511] Fri, 08 September 2017 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So where does the 11.2 client fit in?
Are you running imp on the client or the server?
If it's the client, try the server.
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665522 is a reply to message #665518] Fri, 08 September 2017 04:44 Go to previous messageGo to next message
SiuLong
Messages: 12
Registered: May 2017
Junior Member
Using only server unix machine to exp and imp.

The Client I'm using 11 for the application eg. Toad, Sql Server....
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665528 is a reply to message #665522] Fri, 08 September 2017 07:54 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
That doesn't make any sense to me. Terrible Toad and Sql Server are their own products and do not use an Oracle client. Why even mention it?
Re: IMP-00017: following statement failed with ORACLE error 922 [message #665958 is a reply to message #665528] Wed, 04 October 2017 14:04 Go to previous message
ctvarner
Messages: 11
Registered: August 2017
Junior Member
I love Toad, and while I don't know about yours or OP's, of course, I'm quite certain mine is set up to use Oracle client to connect. To each his own.


SQL Server doesn't make sense, though, unless OP meant SQL+.
Previous Topic: the user data import problem in window 8
Next Topic: Exp in Oracle 12c
Goto Forum:
  


Current Time: Thu Mar 28 14:55:59 CDT 2024