Setting UNDO_RENTENTION

[danghongsinh]

Hi,

I'm setting UNDO_RETENTION=3600. it's mean I retain undo data for 1 hour for read consistency. After 1 hour, if my UNDO tablespace is not full then that undo data would release out of undo segement or keep until UNDO tablespace full?

Thanks in advance,
Sinh

[PAVB]

In your setup Oracle would start releasing undo data after 3600 seconds, it will not wait until undo tablespace gets full.

[danghongsinh]

Thanks for your replying.

If so, My UNDO tablespace often nearly full then it mean my UNDO tablespace is not large enough? But I think my UNDO tablespace is very large (30GB) and I haven't gotten error : "ORA-1555 snapshot too old". Why my UNDO tablespace often nearly full (~98%)?

Thanks in advance,
Sinh

[PAVB]

There are two main reasons for getting ORA-01555...

1- UNDO retention too low
2- Transaction is shooting its own feet

Check alert log and take note how long it takes for transactions to fail with ORA-01555, if longer than 3600 seconds then -in your setup your undo_retention is too low.

I would set undo_retention in a way I could avoid at least 95% of the ORA-01555 occurrences.

In regards to UNDO tablespace sizing... I can't tell you. This is dependent of your application, size of your transactions and undo_retention.
Who knows? may be you have a small database running huge-monster sized transactions.