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

On 23 Jan 2006 12:27:53 -0800, "Daniel Fink" <danielwfink_at_yahoo.com> wrote:

>I can't do troubleshooting for a couple of days so any advice, input,
>insight, etc. is greatly appreciated for the troubleshooting exercise.
>I've googled, metalinked, etc. The problem does not occur in
>development/test, only in production (working on app support to get the
>answer "What is different?")
>
>Both databases are 9.2.0.5 (upgraded from 8.1.7), 64 bit, HP-UX 11, AUM
>
>In dbA at 23:01 each night, 3 user sessions are executing the same
>query and each receive ORA-01555 errors after app. 60 seconds (in
>process of working the application support side of the house to find
>exactly where these queries are coming from). No other 1555s are
>reported during the day.
>
>In dbB at 23:00 each night, a materialized view is being refreshed via
>a dbms_job. The materialized view queries the same 3 tables (via
>database link) as the dbA queries. The refresh runs to completion
>without error.
>
>Has anyone encountered this situation?
>Any thoughts on specific items to examine when troubleshooting, etc.?
>
>Regards,
>Daniel Fink

My experience is ora-1555 goes away by increasing undo_retention_time. I had some ora-1555's during export. The only way to resolve that was increasing undo_retention_time.
Evidently your issue is an issue of result set size, otherwise the problem would have surfaced also in dev/test.

--
Sybrand Bakker, Senior Oracle DBA