Leng,
You didn't mention the frequency of the refresh.
I also don't see mention of which database is generating
the ora-1555 errors.
Jared
| "Kaing, Leng" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 01/13/2004 09:34 PM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: read-only simple snapshot/materialised view refresh |
Hello everyone,
We've got read-only primary key snapshots in our 8.1.7.4 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
Email: [EMAIL PROTECTED]
Phone: +61-3-9203-7589
Mobile: +61-417-371-348
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kaing, Leng
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
