OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » undo-retention=0

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

undo-retention=0 [message #115132]

Thu, 07 April 2005 13:34

hvdtol
Messages: 12
Registered: April 2005

Junior Member

We are migrating Oracle from 817 to 9205.
We have set the undo_retention parameter to 900, but i wonder why to set it.
We have defined the undo tablespace with a max of 2Gb.

When i set the undo_retention=0, will Oracle behave in same same way as the "old" rollback segments, or should i always use a value > 0?
We are not interested in using flash-back queries at all.
Is setting it to zero a safe value?

Thank you for your answer.
Best regards.

Harry vd Tol

Report message to a moderator

Re: undo-retention=0 [message #115137 is a reply to message #115132]

Thu, 07 April 2005 13:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Leave the undo_retention to atleast the default.
Else you may end-up with snapshot too old error.

If the UNDO_RETENTION initialization parameter is not specified, the default value is 900 seconds.

to reset this would be the command.
ALTER SYSTEM SET UNDO_RETENTION = 30000;

quoting docs

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. But for consistent read purposes, long running queries might require old undo information for undoing changes and producing older images of data blocks. The initialization parameter, UNDO_RETENTION, provides a means of explicitly specifying the amount of undo information to retain. With a proper setting, long running queries can complete without risk of receiving the "snapshot too old" error.

Report message to a moderator

Re: undo-retention=0 [message #115139 is a reply to message #115137]

Thu, 07 April 2005 14:04

hvdtol
Messages: 12
Registered: April 2005

Junior Member

I still have my doubts.
The maximum size is 2Gb. If i set the value to 900 seconds, it can still reached the maximum value, which then can leads to the snapshot too old message. Or am i wrong?
For me it looks the same as setting it to zero.

Report message to a moderator