Home » RDBMS Server » Server Utilities » import failed
import failed [message #73844] Thu, 22 July 2004 23:17 Go to next message
uhud
Messages: 1
Registered: July 2004
Junior Member
hi, i imported a .dmp file but i got this error:

Connected to: Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully

Then i run the catexp.sql using SYS user. But i still got that error. Any Suggestions?

Report message to a moderator

Re: import failed [message #73846 is a reply to message #73844] Fri, 23 July 2004 03:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
the dmp file might be created from a different version of exp.
use the same version to import or
install the views ( from the version used to export).

Report message to a moderator

Re: import failed [message #484595 is a reply to message #73846] Wed, 01 December 2010 16:43 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Mahesh,

I exported the dmp file in a 10g client and 'am getting the below error when I try to import it from a 11g client to a 10g dB.

IMP-00058: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00058: ORACLE error 942 encountered
ORA-00942: table or view does not exist

It works fine if I import it to a 11g client to 11g server or 10g client to 10g server. But most of our client machines are equipped with 11g and the servers are in 10g. So I need to get this thing going.

You have mentioned below to install the views as a remedy. Could you give a bit more details on that?

Thanks,
Sharmin

Report message to a moderator

Re: import failed [message #484599 is a reply to message #484595] Wed, 01 December 2010 20:01 Go to previous messageGo to next message
bhadresh4u
Messages: 41
Registered: May 2005
Location: Japan
Member
Hello Sharminjose,

Please visit below link for solution.

http://download.oracle.com/docs/cd/E18283_01/server.112/e17222/expimp.htm#CHDDCIHD

Regards,
Bhadresh Patel

Report message to a moderator

Re: import failed [message #484604 is a reply to message #484599] Wed, 01 December 2010 23:03 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Bhadresh,

I went through the website and it says that,
Export_From-----Import_To-----Export_Version_to_Use-----Import_Version_to_Use
Release 10.2-----Release 11.2 Data Pump-----Export Release 10.2 Data Pump-----Import Release 11.2

In my case, the Import_To is 10.2 and Export_From is 10.2. But the Import_Version_to_Use is 11.2.

If you look at my imp log, it says clearly that the export was done in 10.2 and import is done using 11.2.
Please see below:


Import: Release 11.2.0.1.0 - Production on Wed Dec 1 16:33:03 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing WFDEV's objects into WFDEV
. . skipping TOID validation on type MDSYS.SDO_ORDINATE_ARRAY
. . skipping TOID validation on type MDSYS.SDO_ELEM_INFO_ARRAY
. . skipping TOID validation on type MDSYS.SDO_GEOMETRY
. . importing table "FRAME_DO_STYLES"
IMP-00058: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00058: ORACLE error 942 encountered
ORA-00942: table or view does not exist 1218 rows imported
Import terminated successfully with warnings.

The funniest part is that the import completes successfully.
The command used is below:
imp userid=test_user/test_pass@wfdev file=frame_do_styles.dmp fromuser=WF_USER touser=test_user TOID_NOVALIDATE=MDSYS.SDO_ORDINATE_ARRAY,MDSYS.SDO_ELEM_INFO_ARRAY,MDSYS.SDO_GEOMETRY ignore=y

Any thoughts?

Thanks,
Sharmin

[Updated on: Wed, 01 December 2010 23:46]

Report message to a moderator

Re: import failed [message #484728 is a reply to message #484595] Thu, 02 December 2010 08:09 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sharminjose wrote on Wed, 01 December 2010 17:43
Hi Mahesh,

I exported the dmp file in a 10g client and 'am getting the below error when I try to import it from a 11g client to a 10g dB.


Use a 10g client to import into a 10g database.

Report message to a moderator

Re: import failed [message #484743 is a reply to message #484728] Thu, 02 December 2010 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And before use a 10g client to export.
Always use the lowest version of the tools.

Regards
Michel

Report message to a moderator

Re: import failed [message #484750 is a reply to message #484743] Thu, 02 December 2010 09:35 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Joy/Michael,

I have used 10g client to 10g server and 11g client to 11g server. Both are working fine.
The challenge here is, I have an installer which has a code snippet that imports a table.
This could be run from any clients. Our company has recently upgraded all these clients to 11g, but the server remains in 10g.
So is there any ways, I can import a dmp file from 11g client to 10g server?

Thanks,
Sharmin

Report message to a moderator

Re: import failed [message #484751 is a reply to message #484750] Thu, 02 December 2010 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You repeatedly talk about V10 & V11 HOWEVER
>Connected to: Oracle8i Release 8.1.6.0.0 - Production
>JServer Release 8.1.6.0.0 - Production

original post deals with obsoleted & unsupported V8.1.6
Please clarify.

Report message to a moderator

Re: import failed [message #484752 is a reply to message #484750] Thu, 02 December 2010 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
So is there any ways, I can import a dmp file from 11g client to 10g server?

This question is not clear.
You cannot import a 11g export file into a 10g database (unless you are very lucky).

Regards
Michel

[Updated on: Thu, 02 December 2010 09:50]

Report message to a moderator

Re: import failed [message #484754 is a reply to message #484752] Thu, 02 December 2010 11:40 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Michael,

I had mentioned it before as below:

"In my case, the Import_To is 10.2 and Export_From is 10.2. But the Import_Version_to_Use is 11.2."

I have a 10g export file and needs to be imported from 11g client to 10g server.

Black Swann,
In this post Mahesh talked about the usage of some views, hence I replied back to the same post.

Thanks,
Sharmin

Report message to a moderator

Re: import failed [message #484756 is a reply to message #484754] Thu, 02 December 2010 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You tested it and it failed, so it does not work, so you have to use an appropriate client version.

Regards
Michel

Report message to a moderator

Re: import failed [message #484760 is a reply to message #484756] Thu, 02 December 2010 12:09 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Guys,

I guess if I use expdp it might work with the target server's version.
It is mentioned here (Add http and www before "dbforums.com/oracle/1643901-importing-dump-file-oracle-11g-oracle-10g.html").

Anyways, thanks for the helps.

Thanks,
Sharmin

[Updated on: Thu, 02 December 2010 12:09]

Report message to a moderator

Re: import failed [message #484765 is a reply to message #484751] Thu, 02 December 2010 13:03 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
BlackSwan wrote on Thu, 02 December 2010 10:40
You repeatedly talk about V10 & V11 HOWEVER
>Connected to: Oracle8i Release 8.1.6.0.0 - Production
>JServer Release 8.1.6.0.0 - Production

original post deals with obsoleted & unsupported V8.1.6
Please clarify.


Actually, that was from a different person 6 years ago. New poster jumped onto this thread as it was a similar problem.

Report message to a moderator

Re: import failed [message #484767 is a reply to message #484760] Thu, 02 December 2010 13:06 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sharminjose wrote on Thu, 02 December 2010 13:09
Guys,

I guess if I use expdp it might work with the target server's version.
It is mentioned here (Add http and www before "dbforums.com/oracle/1643901-importing-dump-file-oracle-11g-oracle-10g.html").


You are not really giving good details. And export file created by exp command cannot be used by impdp. It can only be used by imp.

Do you already have the export file and cannot create a new one?

Why not export with 11g client and then import with 11g client. you might get lucky there.

Report message to a moderator

Re: import failed [message #484796 is a reply to message #484767] Thu, 02 December 2010 15:32 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Joy,

I am not really sure what extra details you need.
I said above that,
"In my case, the Import_To is 10.2 and Export_From is 10.2. But the Import_Version_to_Use is 11.2."

I have a 10g export file and needs to be imported from 11g client to 10g server.

Also, I know that the exp file is different from expdp file.
What I plan to do is, imp the 10g export file to 10g and expdp it to a new dmp file with target VERSION 10g.


Again, the import works from a 11g client to 10g server with a 10g export dmp.
But it gives out, table does not exist error even the table exists and with ignore=y option


My assumption is, with this expdp file, it would work if then do an import data pump with any higher clients.
I have still not completed it. If this gets successful, will let you know.

Why not export with 11g client and then import with 11g client. you might get lucky there.
In my case, I can have 10g clients as well as 11g clients, but server will be always 10g.

Thanks,
Sharmin

[Updated on: Thu, 02 December 2010 15:34]

Report message to a moderator

Re: import failed [message #484803 is a reply to message #484796] Thu, 02 December 2010 19:04 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
sharminjose wrote on Thu, 02 December 2010 21:32

I have a 10g export file and needs to be imported from 11g client to 10g server.

So is that an export from a 10g database? Or is it taken with 10g export? Or both?

There are 4 relevant versions here:
Source DB
Target DB
Import
Export

Plus you should get in the habit of stating the full version - 4 decimals.

sharminjose wrote on Thu, 02 December 2010 21:32

Also, I know that the exp file is different from expdp file.
What I plan to do is, imp the 10g export file to 10g and expdp it to a new dmp file with target VERSION 10g.

And what's that going to accomplish apart from making things more compilcated?

sharminjose wrote on Thu, 02 December 2010 21:32

Again, the import works from a 11g client to 10g server with a 10g export dmp.
But it gives out, table does not exist error even the table exists and with ignore=y option

So why not use the 10g import? That would be the actual correct way of doing it as previously stated.

sharminjose wrote on Thu, 02 December 2010 21:32

In my case, I can have 10g clients as well as 11g clients, but server will be always 10g.

What's the point of having 11g clients if the DB is always going to be 10g?

Report message to a moderator

Re: import failed [message #484833 is a reply to message #484803] Fri, 03 December 2010 01:10 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi cookiemonster,

Please find the details below which I already mentioned above with :

[cookiemonster]
So is that an export from a 10g database? Or is it taken with 10g export? Or both?
[me]
The export file is generated using a 10.02.01 exp.exe utility from a 10.02.01 Server.
Mentioned above -> "In my case, the Import_To is 10.2 and Export_From is 10.2. But the Import_Version_to_Use is 11.2."

I apologize for not keeping the 4 decimals separately. But it exists in the import log above
Import: Release 11.2.0.1.0 - Production on Wed Dec 1 16:33:03 2010
Export file created by EXPORT:V10.02.01 via conventional path

[cookiemonster]
And what's that going to accomplish apart from making things more compilcated?
[me]
My requirement is to import this 10.02.01 dmp file to a 10.02.01 server. And let me repeat, this is a part of the installer and it can be run from any client machines which is equipped with either 10g or 11g clients. This dmp files contain some LOB columns and hence could not be given as INSERT statements.

[cookiemonster]
So why not use the 10g import? That would be the actual correct way of doing it as previously stated.
What's the point of having 11g clients if the DB is always going to be 10g?
[me]
As I told above, this is something which is not in my hand. I am a poor consultant. It is a part of the installer and this is the requirement. By hook or crook I should meet it. Sad

Thanks,
Sharmin

Report message to a moderator

Re: import failed [message #484835 is a reply to message #484833] Fri, 03 December 2010 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 02 December 2010 18:49
You tested it and it failed, so it does not work, so you have to use an appropriate client version.

Regards
Michel


"It is not possible" is a valid answer to impossible requirements, even for a poor consultant which first duty is advice before making.

Report message to a moderator

Re: import failed [message #484843 is a reply to message #484835] Fri, 03 December 2010 02:25 Go to previous messageGo to next message
sharminjose
Messages: 8
Registered: December 2010
Location: US
Junior Member
Hi Michel,

"It is not possible" is not a valid answer for me because I already have a work around. If I had only that answer with me, I wouldn't be working. Smile
The work around is pretty straight forward,
1. Keep 2 export files, one from 10g and other from 11g.
2. Determine the Oracle client version from my installer and import the appropriate dmp file.

It is working but does not give a programmer's satisfaction (probably I am too much over concerned about the quality, standards etc....Smile).
I was looking for a better option and wished to see if you could help me.

Thanks,
Sharmin

Report message to a moderator

Re: import failed [message #484845 is a reply to message #484843] Fri, 03 December 2010 02:31 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I was looking for a better option and wished to see if you could help me.

Install a 10g client on all machines.

Regards
Michel

Report message to a moderator

Previous Topic: External table select error
Next Topic: SQL*Loader-350 Syntax error when parsing data type
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 10:59:12 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.