Weird IMP behaviour in 9iR2

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

.

Relevant Pages

  • Re: Weird IMP behaviour in 9iR2
    ... I've got a weird problem trying to load a dump file from an unpatched ... IMP-00017: following statement failed with ORACLE error 2270: ... the LOGICCONNECTIVITY table is to blame. ... LOGICCONNECTIVITY in some way but Oracle can't enable the constraints ...
    (comp.databases.oracle.misc)
  • Re: Import Question
    ... as that is one of the last tasks imp completes before starting the ... when importing a properly executed full export. ... Maybe in some antiquated, pre-1987 release of Oracle, but recent ... versions constraints are the last task ...
    (comp.databases.oracle.server)
  • Re: Give user index grants Oracle 8/9i
    ... I'm new to Oracle, so I have a question that might be easy... ... see the constraints on the table, as I can see by checking the ... schema owner (except perhaps in a development environment). ... Oracle does have some special roles such as DBA that perhaps could ...
    (comp.databases.oracle.server)
  • Re: Limit UPDATE statements to affect only one row
    ... P.S. Fight the use Hibernate in all circumstances unless you want to ... Oracle does not enforce the ... constraints but the definitions would be there. ...
    (comp.databases.oracle.server)
  • Re: Is there any predefined Oracle constants there denominate very large number ?
    ... Mark D Powell wrote: ... Do you mean is there a limit to how many check constraints or FK constraints you can define on a table? ... many features require a certain version of Oracle. ...
    (comp.databases.oracle.server)