Problem importing Oracle .dmp file

Question

So I have looked at all the suggested ways of importing .dmp files and non of them seem to answer this question: where does the data go once you import it?

Context: I created a user like so:

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

I then 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 one it asked me for the next one and then I received the message "Import terminated successfully with warnings."

The warning was:

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 it says it was terminated successfully so my assumption (I am new to oracle so this may be wrong) is that the data was loaded. However, when I use SQL developer to connect to the database and look under the 'tables' node under the IMPORTER user, there is nothing there. What is going on? Did the data load? If so, where can I find it?

Solution 1

Are you sure that the file actually contains object owned by OVIEDOE ? Normally I'd expect a list of the objects being imported as it processes them.

This result is what I'd expect if I'd done an export for USER1 and then tried to import with FROMUSER=USER_1 (and yes, I've done plenty of typos in my time).

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

Solution 2

In SQL Developer, are you logged in as IMPORTER? The user you're logged in as must have privileges on the IMPORTER schema to see its objects.

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

Solution 3

DCookie: Turns out system did not have IMP_FULL_DATABASE privileges, I have 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

the result was:

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's objects into SYSTEM
. importing PDQ_CLNR's objects into PDQ_CLNR

After this there were a series of alternating blocks that have what looks like the creation of tables followed by the skipping of a table like:

   . . skipping table "CHAINED_ROWS"

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

Import file: EXPDAT.DMP >

Now I don't know what this dump file is or why it is expected. Any ideas?