ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES
Valid For
Replicat
Description
Use ALLOWNOOPUPDATES
and NOALLOWNOOPUPDATES
to control how Replicat responds to a no-op
operation. A no-op operation is one in which there is no effect on the target table. The following are some examples of how this can occur.
-
The source table has a column that does not exist in the target table, or it has a column that was excluded from replication (with a
COLSEXCEPT
clause). In either case, if that source column is updated, there will be no target column name to use in theSET
clause within the Replicat SQL statement. -
An update is made that sets a column to the same value as the current one. The database does not log the new value, because it did not change. However, Oracle GoldenGate captures the operation as a change record because the primary key was logged, but there is no column value for the
SET
clause in the Replicat SQL statement.
When NOALLOWNOOPUPDATES
is
used, Replicat only abends if the source and target tables do not have a key
defined, or the Replicat does not use KEYCOLS
. In such cases,
wherein the target table has no unique key defined and an update operation is
carried out on any of the source columns, an error similar to the following
occurs:
Encountered an update for target table TELLER, which has no
unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to
process the update without applying it to the target database. Use
APPLYNOOPUPDATES to force the update to be applied using all columns in both the
SET and WHERE clause.
You can use the parameter APPLYNOOPUPDATES
to force the
UPDATE
to be applied. APPLYNOOPUPDATES
overrides ALLOWNOOPUPDATES
. If both are specified, Replicat applies
updates for which there are key columns for the source and target tables.
If ALLOWNOOPUPDATES
is specified when the HANDLECOLLISIONS
or INSERTMISSINGUPDATES
parameter is being used, and if Oracle GoldenGate has all of the target key values, Oracle GoldenGate applies an UPDATE
by using all of the columns of the table in the SET
clause and the WHERE
clause (invoking APPLYNOOPUPDATES
behavior). This is necessary so that Oracle GoldenGate can determine whether the row is present or missing. If it is missing, Oracle GoldenGate converts the UPDATE
to an INSERT
.
Default
NOALLOWNOOPUPDATES
Syntax
ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES