Re: Weird IMP behaviour in 9iR2

On Jul 27, 4:06 am, Tarby777 <nick_william..._at_mentorg.com> wrote:
> 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

Do this:

imp file=their_file.dmp fromuser=their_user touser=my_user indexfile=my_log_list.txt

You need to find out what Oracle is trying to do when it creates this table, and the above command should show you that.

You may find your answer in the output.

David Fitzjarrell Received on Fri Jul 27 2007 - 08:06:10 CDT