ROW ID Mismatch with Fast Refresh on BIGFILE vs. SMALLFILE ORA-1 (Doc ID 1665434.1)

Last updated on FEBRUARY 24, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

An Application with updates to a PS_Customer table is audited.  The audit information is based on MLOG$ (MV) on the base table.

Previous functionality in 10gR2 was to track the rowid of the Changes made to the base table which would create an update in the MLOG$.

- If there was an update (or delete) shortly after or anytime prior to the fast refresh, you would only find ONE row in the MLOG$ for that particular entry + their table RowID.

Example:
SQL>  select rowid from <table>;

ROWID
------------------
AAAXZzAAAAAAAAXAAA

SQL> SQL>   select * from <MLOG$>
M_ROW$$
--------------------------------------------------------------------------------
D
-
AAAXZzAAAAAAAAXAAA
I

AAAXZzAAAAAAAAXAAA

Two rows tracked in the MLOG$ when tables created on BIGFILE Tablespace as of 11g Release 2, multiple rows with different rowids are created when doing the update.

This was verified to work as it did in 10gR2 with the table on SMALLFILE tablespace, but fails when the table is on BIGFILE tablespace.

Fast Refresh can fail with ORA-1.

Changes

 Moved tables to BIGFILE tablespace when moved the application to 11g.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms