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

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Wed, 25 Jan 2006 18:24:51 GMT
Message-ID: <TnPBf.9649$zh2.3314@trnddc01>

Mladen Gogala wrote:
> On Mon, 23 Jan 2006 21:59:28 +0000, Chuck wrote:
>

>> Size of the undo_ts in this case is irrelevant if as you say it never
>> gets > 50% full.
>>
>> If your undo_ts is large enough to hold 2x undo_retention at peak undo
>> rate, the TS will never get > 50% used. If you are getting ora-1555 it
>> is probably because undo_retention is set too low.
>>
>> How long have the queries on dbA been running when they fail? Probably
>> longer than undo_retention.

>
>
> Contrary to the popular opinion, size does matter. If you set
> UNDO_RETENTION to 3 days, Oracle will try protecting undo blocks
> for 3 days before allowing you to reuse them. In order for that
> war on ORA-1555 to succeed, you must have enough fresh blocks so
> that the previously used blocks are kept in the frig for 3 days.
> That's how size comes into the play.
>

The OP stated that the undo_ts never gets > 50% full making the size irrelevant.

If you run out of space in the undo_ts, Oracle will start reusing committed undo space even if it's not older than undo_retention. Undo_retention is only a guideline, not an absolute.

-- 
To reply by email remove "_nospam"

Received on Wed Jan 25 2006 - 12:24:51 CST