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?
Best Answer
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.