ORA-01779: The columns corresponding to the non-key save table cannot be modified

When I updated a view, I encountered this error: ORA-01779: could not modify the column corresponding to the non-key save table

01779,000 00, "cannot modify a column which maps to a non key-preserved table"
// * Cause: An attempt was made to insert or update columns of a join view which
// Map to a non-key-preserved table.
// * Action: Modify the underlying base tables directly.

You can simulate this error:

SQL> desc gw2
Name Type Nullable Default Comments
-------------------------------------
SID INTEGER Y
OLD_SID INTEGER Y

SQL> select * from gw1;
 
ID SID
------------------------------------------------------------------------------
1 1
2 2
3 3
4
 
SQL> select * from gw2;
 
SID OLD_SID
------------------------------------------------------------------------------
1 11
2 22
3 33
 
SQL> update (select gw1.sid, gw2.old _ sid from gw1, gw2 where gw1.sid = gw2.sid) s set s. sid = s. old_sid;
 
Update (select gw1.sid, gw2.old _ sid from gw1, gw2 where gw1.sid = gw2.sid) s set s. sid = s. old_sid
 
ORA-01779: The columns corresponding to the non-key save table cannot be modified

This is because one sid in gw1 cannot correspond to the sid of a unique gw2 table. That is to say, the sid of gw2 cannot be unique. The solution is to add the unique constraint to the sid of gw2.

SQL> alter table gw2 modify sid unique;
 
Table altered
 
SQL> update (select gw1.sid, gw2.old _ sid from gw1, gw2 where gw1.sid = gw2.sid) s set s. sid = s. old_sid;
 
3 rows updated

In this way, you can.

GoldenGate performs two-way Oracle-Oracle replication without using a Data Pump

One-way Oracle-Oracle Replication Using the GoldenGate Data Pump

How to debug Oracle Data Pump (expdp/impdp)

Oracle Database Export data pump (EXPDP) file storage location

Export of Oracle 10g Data Pump Partition Table