Setting UNDO_RENTENTION

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

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

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

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.