What is the difference between "copy table" and "append table"? (4296988)

Starting with Shareplex 6.0 and up, target tables can be synchronized following a new approach, namely using the commands "copy table" or  "append table". The two approaches have different algorithm when synchronizing the target table. To be able to sync the table correctly, one needs to understand the subtleties of the two commands.

N.A.

The "copy table" command syncs the target table by truncating it and then importing the data from source table to target table using Oracle's Export/Import with CONSISTENT=y. This results in the synchronization of the target table.

The "append table" on the other hand also uses Oracle's Export/Import with CONSISTENT=y to sync the target table but instead of truncating the target table and then importing the data into it, it simply appends the data to the target table. This would sync the target table if there was no data in it. However, if there is data in it, this would result in the target table going out of sync with the source table instead of being synchronized by it. In fact, it may have more rows than the source table, once the command finishes. The following illustrates this point assuming that the source table named table1 is replicating to the target table with the same name:

A. Before "append table" is issued, the source table has one more row than the target table:

Source data:

SQL> select * from table1;

   NUM_COL
----------
         1
         2
         3

Target data:

SQL> select * from table1;

   NUM_COL
----------
         1
         2

B. The "append table" is issued assuming that the source and target schema is same:

sp_ctrl (source_servername:port_num)> append owner.table1

C. Once the "append table" finishes, the target has the original rows as well as the rows from the source appended to it as shown:

Target data:

SQL> select * from table1;

NUM_COL
----------
         1
         2
         1
         2
         3
This results in the target table having more rows as compared to the source table. So the "append table" may not be suitable for synchronizing the target table unless the target table is manually truncated before the "append table" is issued.