Re: ORA-01555 on source database for materialized view refresh across link

Specific thoughts - bear with me as I think this through:

ORA-01555 means that the transaction undo information can not be found in the undo segment. That occurs when a query starts during the transaction, the transaction completes, the 'slot' in the undo area is needed for another transaction, and the query can no longer find the original transaction.

You state that the problem does not occur in dev/test. That may imply

1. the query is taking a lot longer in prod than in dev/test;
2. the number of transactions is a lot higher in prod than in d/t;
3. the transactions are a lot shorted (even aborted) in prod;
4. transactions are different.
   (or other things I have not mentioned).
   
5. could be caused by pure data volume; by incorrect statistics (stale, not current, [not] using histograms where appropriate); assuming RBO when CBO is kicked in due to partial statistics; using a bad estimate statistics value; using bind variables (as they ignore histograms); not having the right indexes.

b,c,d) also potentially related to volume, but more from the updater's side, this could be caused by frequent commits/rollbacks inside loops; DDL
(create/drop temp tables) which cause unplanned commits; error handling
unexpectedly being invoked; different data characteristics in prod than in test.

It would be interesting to take statspack snapshots and try to determine the general area of effort causing the 1555s, but the simplest thing to try is make the undo available 'even bigger'.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***