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 126.96.36.199.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 188.8.131.52.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 184.108.40.206.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?