Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> read-only simple snapshot/materialised view refresh

read-only simple snapshot/materialised view refresh

From: Kaing, Leng <>
Date: Tue, 13 Jan 2004 21:34:24 -0800
Message-ID: <>

Hello everyone,

We've got read-only primary key snapshots in our databases. 1 master. 1 slave. master and slave are on different servers. Snapshots are refreshed by the "FAST" method using dbms_refresh.refresh. However, do to the extremely high transaction rates on our database, we're getting ORA-1555 when trying to refresh the snapshots. The mlog$ tables builds up and the slave just keeps on falling behind. From what I can see, snapshots are refreshed as a single large transaction. So if there are 500K rows in the mlog$ table, all 500K will be processed in one go. There are no intermediate commits.

So my question is: how do you specify a commit point with snapshots? I'm looking for parameters similar to that of the exp and sqlldr utility where you can specify commit points. I've logged an iTAR with Oracle Support and there answer is that it's not possible. ARGH!!

Here's another crazy question is - has anyone updated the dbms_refresh package to add a commit point?

Or, have you tried to interogate the mlog$ and write a PL/SQL procedure to process the rows in there, thereby having your own commit points? mlog$ provides the primary keys and the DML type. So surely it's just a matter of going through each one of the row and applying it to the slave?

TIA, Leng,

Leng Kaing
Phone: +61-3-9203-7589
Mobile: +61-417-371-348


Please see the official ORACLE-L FAQ:

Author: Kaing, Leng

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 13 2004 - 23:34:24 CST

Original text of this message