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 FETCHCOLS
clause of a TABLE
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] [, 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 FETCHCOLS (*)
in the TABLE
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.
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
and USEROWID
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
and USEROWID
are specified, ROWID
takes priority for faster access to the record. USEROWID
is the default.
Examples
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
MAXFETCHSTATEMENTS 150
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