Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Using COPY to import data from another database (part of imp/exp problem)

Using COPY to import data from another database (part of imp/exp problem)

From: C Chang <cschang_at_maxinter.net>
Date: Mon, 27 Jan 2003 23:40:12 -0500
Message-ID: <3E3609AC.4405@maxinter.net>


This is another problem when I tried to use COPY command in SQL plus to import the data from another database. I tried to import the data from a table named list_data to a same list_data table of another database. Two of the column are the foreign keys of another table list_sources ( as below ). However even I disable the LIST_SOURCES_FK and the parimary key of list_data on both the source and destinated databases, I still faced the error of - "ORA-02449: unique/primary keys in table referenced by foreign keys". What should I do to complish the COPY? Following are the scripts I did for both list_data and list_sources table and the COPY scripts.

ALTER TABLE list_data
DISABLE PRIMARY KEY; ALTER TABLE list_sources
DISABLE CONSTRAINT LIST_SOURCES_FK;

OWNER      CONSTRAINT_NAME                C
TABLE_NAME                     R_CONSTRAINT_NAME              STATUS
---------- ------------------------------ -
------------------------------ ------------------------------ --------
MyPRICE  SYS_C001166                    C
LIST_DATA                                                     ENABLED
MyPRICE  SYS_C001167                    C
LIST_DATA                                                     ENABLED
MyPRICE  SYS_C001168                    C
LIST_DATA                                                     ENABLED
MyPRICE  LIST_DATA_PK                   P
LIST_DATA                                                     DISABLED


OWNER      CONSTRAINT_NAME                C
TABLE_NAME                     R_CONSTRAINT_NAME              STATUS
---------- ------------------------------ -
------------------------------ ------------------------------ --------
MyPRICE  SYS_C001170                    C
LIST_SOURCES                                                  ENABLED
MyPRICE  SYS_C001171                    C
LIST_SOURCES                                                  ENABLED
MyPRICE  LIST_SOURCES_FK                R LIST_SOURCES                  
LIST_DATA_PK                   DISABLED

SQL> COPY FROM myprice/myself_at_prod TO myPrice/owner_at_test -
> REPLACE list_data -
> USING SELECT * FROM list_data

Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80)

ERROR:
ORA-02449: unique/primary keys in table referenced by foreign keys

SYStem: Oracle 8.1.6 on NT4 with 2X 750 Mhz CPU with 1G RAM and 6 HDs. Received on Mon Jan 27 2003 - 22:40:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US