The "copy tablename" command is launched to sync the target table named table2. On target the foreign key constraint is enabled whereby the primary key of this table is referenced as foreign key by a column in another table named table1. The copy fails as follows:
sp_ctrl (source_server:port_num)> copy owner.table2
copy started; job id 81
sp_ctrl (source_server:port_num)> copy status
Job ID : 81
Host : source_server
Started : 13-JAN-09 19:27:16
Job Type : Copy
Status : Failed - Could not truncate target table
ID Tablename Total Rows %Comp Status Status Time Total Time
------ ------------------------------------ ---------- ----- ---------- ----------- ----------
1 "OWNER"."TABLE2" 2 Failed
N/A 0:27
The copy log (sync client log) shows that it failed with ORA-02266 as follows:
Tue Jan 13/19:27:43.407:: Truncating tables
Tue Jan 13/19:27:43.441:: Truncating table "OWNER"."TABLE2"
Tue Jan 13/19:27:45.743:: ERROR: ORA-2266: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
at sync/oci/sync_ociquery.cpp:174 at sync/oci/sync_ociquery.cpp:199
Tue Jan 13/19:27:45.744:: ERROR: Failed to truncate table OWNER.TABLE2 at sync/clt/sync_table_handler.cpp:197
Tue Jan 13/19:27:45.745:: ERROR: Failed to truncate tables at sync/clt/sync_clt_messager.cpp:933 at sync/clt/sync_clt_messager.cpp:413
Tue Jan 13/19:27:45.746:: ERROR: An error occured at sync/clt/sync_clt_messager.cpp:341
Tue Jan 13/19:27:45.746:: Abort remaining threads
The foreign key constraint is interfering with copy during the truncate phase.
WORKAROUND:
a. Disable the constraint on the target as below:
alter table table1 drop constraint <constraint_name>
b. Run the copy
c. Enable the constraint once again after the copy succeeds. It may be possible that the constraint may fail to enable if the data on target table after the copy is inconsistent with the constraint definition. In that case one can enable the constraint with NOVALIDATE clause or keep the constraint disabled at all times or refrain from using copy on this table altogether. Normally the constraints are disabled on target since they are enabled on source anyway.
During the synchronization using "copy table", the sync client first truncates the target table, then it renames the target table to the name of the source table. It then imports the data from the source table to the renamed target table using Oracle Import. Once the import finishes, it renames the newly populated target table back to the original name of the target table. When attempting to truncate the target table as the first step, it runs into the ORA-02266.
Here is the description of the Oracle error:
ORA-02266 Error Code Description:
Error ORA-02266
Description unique/primary keys in table referenced by enabled foreign keys
Cause An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.
Action Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center