I am working on a site that is replicating tables between databases using Materialized Views. I have realised that if a replicated table is truncated on the source database, that the rows remain in the materialized view on the target until a complete refresh is performed. Worse, if ROWID based Materialized View is used, then the fast refresh process will error with 'ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh'. In PeopleSoft, there are no primary keys, and it is not possible to build them if any of the key columns are nullable, which is the case with non-required date fields in PeopleSoft.
Here is an example. I will create two tables, and replicate them with Materialized Views. One by primary key, and the other by ROWID.
So the data has replicated and everything seems to working fine. Now lets truncate the source tables, and put some new data in. One row has the same key value, one does not.
The fast refresh of the Materialized View with the primary key appears to work. The new rows are inserted into the Materialized View, replacing existing rows with the same key values, but the old rows remain.
The fast refresh of the Materialized View by ROWID fails, and the old data remains in place.
But rows 1 & 2 are in MV but not source table
However, a full refresh corrects the discrepancy
It seems to me, that it would be appropriate to prevent a Truncate command executing if there is a Materialized View Log on the table. I have written such a trigger
The trigger MVTRUNC_LOCK can be downloaded from the Go-Faster website.