FETCHOPTIONS
Valid For
Extract
Description
Use the FETCHOPTIONS
parameter to control certain aspects of the way that Oracle GoldenGate fetches data in the following circumstances:
-
When the transaction record does not contain enough information for Extract to reconstruct an update operation.
-
When Oracle GoldenGate must fetch a column value as the result of a
MISSINGCOLS
clause of aTABLE
statement.
FETCHOPTIONS
is table-specific. One FETCHOPTIONS
statement applies for all subsequent TABLE
statements until a different FETCHOPTIONS
statement is encountered.
Default fetch properties are adequate for most installations.
Default
Ignore missing rows and continue processing
Syntax
FETCHOPTIONS [, FETCHPKUPDATECOLS] [, MISSINGCOLS] [, INCONSISTENTROW action] [, MAXFETCHSTATEMENTS number] [, MISSINGROW action] [, NOFETCH] [, SUPPRESSDUPLICATES] [, USEKEY | NOUSEKEY] [, USELATESTVERSION | NOUSELATESTVERSION] [, USESNAPSHOT | NOUSESNAPSHOT] [, USEROWID | NOUSEROWID]
-
FETCHPKUPDATECOLS
-
Fetches all unavailable columns when a primary key is updated. This option is off by default. When off, column fetching is performed according to other
FETCHOPTIONS
options that are enabled.When on, it only takes effect during an update to a primary key column. The results are the same as using
(*)
in theTABLE
statement.LOB
columns are included in the fetch.Use this parameter when using
HANDLECOLLISIONS.
When Replicat detects a missing update, all of the columns will be available to turn the update into an insert. -
MISSINGCOLS
-
Fetches any missing columns from update and delete operations, including LOB columns. This option is only valid for Oracle Database. It can negatively impact the database and the Extract performance due to additional queries to fetch the data. Especially if there are large LOB values that need to be fetched and written to the trail.
Setting this parameter is the same as setting the following parameters:MISSINGCOLS(*) in the TABLE statement NOCOMPRESSDELETES FETCHMISSINGCOLS GETUPDATEBEFORES NOCOMPRESSUPDATES LOGALLSUPCOLS
However, settingFETCHOPTIONS MISSINGCOLS
conflicts with the following parameters:FETCHOPTIONS NOFETCH FETCHOPTIONS FETCHPKUPDATECOLS COMPRESSDELETES COMPRESSUPDATES GETUPDATEBEFORES LOGALLSUPCOLS
-
INCONSISTENTROW
action
Indicates that column data was successfully fetched by row ID, but the key did not match. Either the row ID was recycled or a primary key update occurred after this operation (and prior to the fetch).
action
can be one of the following:-
ALLOW
-
Allow the condition and continue processing.
-
IGNORE
-
Ignore the condition and continue processing. This is the default.
-
REPORT
-
Report the condition and contents of the row to the discard file, but continue processing the partial row.
-
DISCARD
-
Discard the data and do not process the partial row.
-
ABEND
-
Discard the data and quit processing.
-
-
MAXFETCHSTATEMENTS
number
-
Controls the maximum allowable number of prepared queries that can be used by Extract to fetch row data from a source database. The fetched data is used when not enough information is available to construct a logical SQL statement from a transaction log record. Queries are prepared and cached as needed. When the value set with
MAXFETCHSTATEMENTS
is reached, the oldest query is replaced by the newest one. The value of this parameter controls the number of open cursors maintained by Extract for fetch queries only. Additional cursors may be used by Extract for other purposes, such as those required for stored procedures. This parameter is only valid for Oracle databases.The default is 100 statements. Make certain that the database can support the number of cursors specified, plus cursors used by other applications and processes. -
MISSINGROW
action
-
Provides a response when Oracle GoldenGate cannot locate a row to be fetched, causing only part of the row (the changed values) to be available for processing. Typically a row cannot be located because it was deleted between the time the change record was created and when the fetch was triggered, or because the row image required was older than the undo retention specification.
action
can be one of the following:-
ALLOW
-
Allow the condition and continue processing. This is the default.
-
IGNORE
-
Ignore the condition and continue processing.
-
REPORT
-
Report the condition and contents of the row to the discard file, but continue processing the partial row.
-
DISCARD
-
Discard the data and do not process the partial row.
-
ABEND
-
Discard the data and quit processing.
-
-
NOFETCH
-
Prevents Extract from fetching the column from the database, this option is off by default. Extract writes the record to the trail, but inserts a token indicating that the column is missing.
-
SUPPRESSDUPLICATES
-
Valid for Oracle. Avoids target tablespaces becoming overly large when updates are made on LOB columns. By default,
SUPPRESSDUPLICATES
is set to off. For example, after replication a source tablespace of 232MB becomes a target tablespace of 7.52GB. -
USEKEY | NOUSEKEY
-
Determines whether or not Oracle GoldenGate uses the primary key to locate the row to be fetched.
If both
USEKEY
andUSEROWID
are specified,ROWID
takes priority for faster access to the record.USEROWID
is the default. -
USELATESTVERSION | NOUSELATESTVERSION
-
Valid for Oracle. Use with
USESNAPSHOT
. The default,USELATESTVERSION
, directs Extract to fetch data from the source table if it cannot fetch from the undo tablespace.NOUSELATESTVERSION
directs Extract to ignore the condition if the snapshot fetch fails, and continue processing.To provide an alternate action if a snapshot fetch does not succeed, use the
MISSINGROW
option. -
USESNAPSHOT | NOUSESNAPSHOT
-
Valid for Oracle. The default,
USESNAPSHOT
, causes Extract to use the Oracle Flashback mechanism to fetch the correct snapshot of data that is needed to reconstruct certain operations that cannot be fully captured from the redo record.NOUSESNAPSHOT
causes Extract to fetch the needed data from the source table instead of the flashback logs. -
USEROWID | NOUSEROWID
-
Valid for Oracle. Determines whether or not Oracle GoldenGate uses the row ID to locate the row to be fetched.
If both
USEKEY
andUSEROWID
are specified,ROWID
takes priority for faster access to the record.USEROWID
is the default.
Examples
- Example 1
-
The following directs Extract to fetch data by using Flashback Query and to ignore the condition and continue processing the record if the fetch fails.
FETCHOPTIONS USESNAPSHOT, NOUSELATESTVERSION
- Example 2
MAXFETCHSTATEMENTS 150
- Example 3
-
The following directs Extract to fetch data by using Flashback Query and causes Extract to abend if the data is not available.
FETCHOPTIONS USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW ABEND
Parent topic: Oracle GoldenGate Parameters