Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a screwy problem using imp on an Oracle 11gR2 instance to try and import export files created with exp on an Oracle 10g instance (different hardware, if that matters).

I've read through the Oracle documentation and I know that I can use imp from any higher version to import *.dmp files created from any lower version of Oracle - or at least it shouldn't be a problem going from 10g to 11gR2.

Note: I can't use DataPump (tried) because the 10g is 10.2.0.1 which has a known PL/SQL bug when trying to use DataPump from a remote DB; the solution is to patch to at least 10.2.0.2, but our environment is too unstable to do that at this time.

We have a nightly job that exports schema-level *.dmp files from the Oracle 10g instance for all of the active users and places them on a common network share.

We've previously exported the user schemas from the Oracle 10g instance and used those exports to create those same schemas on the Oracle 11gR2 instance, and while that was several months ago, most of the schemas haven't changed much - there are a couple of new tables, but the imp should be able to handle that just fine.

So, before I wrote a giant parameter file and ran a mass import, I decided to do a small-scale test with one table from one schema - the schema isn't mine, but ultimately I can fix that. I just want to make this work first - so I do:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\user1.dmp tables=(zomg_must_have_dataz)

But then I get this:

Import: Release 11.2.0.1.0 - Production on Tue Dec 10 21:32:17 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

Export file created by EXPORT:V10.02.01 via direct path

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

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing USERGOD's objects into DIGITALNOISE
IMP-00033: Warning: Table "ZOMG_MUST_HAVE_DATAZ" not found in export file
Import terminated successfully with warnings.

Which leaves me really confused, because:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\USER1.dmp show=Y full=Y LOG=USER1.LOG

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

Export file created by EXPORT:V10.02.01 via direct path

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

 "ALTER SESSION SET CURRENT_SCHEMA= "USER1""
 "CREATE TABLE "ZOMG_MUST_HAVE_DATAZ" (<<SNIP>>)  PCTFREE 10 PCTUSED 40 INITRANS "
 "1 MAXTRANS 255 STORAGE(INITIAL 540016640 FREELISTS 1 FREELIST GROUPS 1 BUFF"
 "ER_POOL DEFAULT) TABLESPACE "ZOMG" NOLOGGING NOCOMPRESS"
. . skipping table "ZOMG_MUST_HAVE_DATAZ"

Clearly indicates that the table ZOMG_MUST_HAVE_DATAZ exists in the USER1.DMP file - but when I got to actually import it, imp says that it doesn't exist...

My user ID has the IMPORT FULL DATABASE system privilege on the target via Role assignment, so I don't think that's the issue.

Am I missing something rather obvious here?

share|improve this question
    
Try adding a REMAP_SCHEMA parameter –  Phil Dec 11 '13 at 10:12
    
Did you try with conventional path export (no direct path) Also: are the nls settings for the exp and imp clients correct? –  ik_zelf Dec 11 '13 at 12:17
    
@Phil - REMAP_SCHEMA appears to be a impdp parameter, not a imp one, however the equivalent appears to be FROMUSER/TOUSER - and that worked! Now to see if I can actually get all of them to import - should I have to specify FROMUSER/TOUSER if I want the objects to go into the Schemas that they came from? –  DigitalNoise Dec 11 '13 at 18:13
    
Oh, sorry. I assumed imp/exp died a death years ago and didn't read your question correctly :) My apologies. –  Phil Dec 11 '13 at 23:52

1 Answer 1

Your table is owned by USERGOD, but you are importing as DIGITALNOISE. Yes, you have the IMPORT FULL DATABASE, but you are not doing a full import.

Using FROMUSER/TOUSER is one solution. Performing a full import by adding the FULL=Y option is another.

share|improve this answer
    
The export was done by USERGOD - the table is owned by USER1's schema. The piece that I was missing was that I needed to specify a TOUSER. –  DigitalNoise Dec 13 '13 at 14:45
    
Ok, doesnt change the problem, because its still a different user. –  Balazs Papp Dec 13 '13 at 18:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.