Problem importing an .dmp Oracle file.

Question

So, I examined all the proposed methods for importing .dmp files, and they do not answer this question: where does the data go after they are imported?

Context: I created the user as follows:

SQL> create user IMPORTER identified by "12345"; SQL> grant connect, unlimited tablespace, resource to IMPORTER; 

Then I ran the "imp" command as follows:

 C:\>imp system/password FROMUSER=OVIEDOE TOUSER=IMPORTER file=c:\database1.dmp 

Now there were 9 .dmp files, after each of them he asked me about the following, and then I received the message "Import completed successfully with warnings."

Warning:

 Warning: the objects were exported by OVIEDOE, not by you import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) IMP-00046: using FILESIZE value from export file of 2147483648 

Now he says that it was completed successfully, so my assumption (I am new to oracle, so this may be wrong) is that the data was loaded. However, when I use the SQL developer to connect to the database and browse under the “tables” node under the user IMPORTER, there is nothing there. What's happening? Did you download the data? If so, where can I find it?

Answer 1

In SQL Developer, are you logged in as IMPORTER? The user with whom you are logged in must have privileges in the IMPORTER scheme to see its objects.

Also, make sure that the SYSTEM user has IMP_FULL_DATABASE privileges.

Answer 2

Are you sure the file actually contains an object owned by OVIEDOE? Usually I expect the list of imported objects to be processed by them.

I would expect this result if I did an export for USER1 and then tried to import with FROMUSER = USER_1 (and yes, I made a lot of typos in my time).

Try IMP SHOW = Y to display the contents of the dump file.

Answer 3

DCookie: It turns out that the system did not have IMP_FULL_DATABASE privileges, I granted them.

So, I tried to run:

 C:\oraclexe\app\oracle\product\10.2.0\server\BIN>imp.exe system/password file=C:\exp_schema\database1.dmp show=y full=y 

result:

 Import: Release 10.2.0.1.0 - Production on Mon Apr 19 18:21:11 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Export file created by EXPORT:V09.02.00 via conventional path Warning: the objects were exported by OVIEDOE, not by you import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) IMP-00046: using FILESIZE value from export file of 2147483648 . importing OVIEDOE objects into SYSTEM . importing PDQ_CLNR objects into PDQ_CLNR 

After that there was a series of alternating blocks that have what looks like table creation, followed by a table skip, for example:

  . . skipping table "CHAINED_ROWS" 

There is no indication as to why these tables were omitted, and there are many. Then, at the end, instead of asking for the following file (which is database2.dmp), it stops and displays:

 Import file: EXPDAT.DMP > 

Now I do not know what the dump file is or why it is expected. Any ideas?

Answer 4

It is possible that "imp" created an OVIEDOE schema for you, then you need to look there (in the SQL Developer open "Other Users" node).

And if that is not what you want, try the touser / touser imp switches.