Weird IMP behaviour in 9iR2

From: Tarby777 <nick_williamson_at_mentorg.com>
Date: Fri, 27 Jul 2007 09:06:29 -0000
Message-ID: <1185527189.602564.60950@o61g2000hsh.googlegroups.com>

Hi all,

I've got a weird problem trying to load a dump file from an unpatched 9iR2 installation into my own 9.2.0.8 set-up. I get the same thing if I try to import into 10.2. The import appears to go well, and only encounters problems after loading the table contents, at the point where constraints are enabled. I get lots of errors like this, for different tables:

IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "CHSASSEMBLY" ADD CONSTRAINT "ASSEMBLY_FK" FOREIGN KEY
("CONNEC"
"TIVITY_ID") REFERENCES "LOGICCONNECTIVITY" ("CONNECTIVITY_ID") ON
DELETE CA"
"SCADE ENABLE NOVALIDATE"

IMP-00003: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list

In each case, the LOGICCONNECTIVITY table is to blame. The tables that get these errors are all supposed to be constrained by LOGICCONNECTIVITY in some way but Oracle can't enable the constraints because LOGICCONNECTIVITY doesn't have a primary key constraint of its own. Looking in the database after loading the dump, the LOGICCONNECTIVITY table is there but it is missing the primary key and uniqueness constraints that it should have, and the table contains no rows.

Now, where it gets weird is that the customer's DBA has sent us some screenshots showing that the constraints exist in his database, and the table contains data. It's just that when we load the dump, the constraints and the data are missing from the LOGICCONNECTIVITY table. When Oracle is putting out this kind of info during the load:

. . importing table                       "UGROUP"          1 rows
imported
. . importing table                      "UP_FORM"          0 rows
imported
. . importing table                     "UP_PARAM"          0 rows

imported

...the LOGICCONNECTIVITY table isn't in the list. It's like there's just something different about it that is causing Oracle to handle it differently to the other tables, but I don't know what. We used to be able to import data from this customer... does anyone know what is going on?

imp file=their_file.dmp fromuser=their_user touser=my_user rows=y indexes=y constraints=y grants=n log=my_log.txt

Thanks,
Nick Received on Fri Jul 27 2007 - 04:06:29 CDT