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

C Chang wrote:
>
> 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.
I found the solution for using the COPY ..REPLACE to transfer from one database to another database. Because there is a second table on the destination referencing with foreign key on the destined table, I have to drop, not DISABLE, the foreign key on that table. Then I can use the COPY command to do the job. But this immediately raises a question from such COPY process. How can I use the copy process to transfer a table which contains a column that is a referencing to another data type of which is defined by the user.

Besides, I found out that the COPY ..REPLACE process consumes too much overhead to accomplish the transferring of data. After I COPY the data from A to B, I need to reconstruct the new PRIMARY key on B, constraint key on child table and missing Index too, probably another constraint too or any relationship which was associated to B before I COPY.

C CHANG Received on Tue Jan 28 2003 - 20:17:29 CST