RE: Undo Tablespace issue

Hi Yudhi

My understanding is that with a long running SQL statement, the database needs to keep all the undo generated since the SQL started to be able to provide read consistency<https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-6A252EAE-7900-47DC-BD6A-D5376A6B7608>. So it’s not just the undo used by the transaction itself which contributes to filling the undo tablespace, it’s all the undo that has been generated by all transactions since the start of the SQL. It is no longer needed by those transactions for rollback, as they have been committed, but it is needed by your transaction for read consistency. I am assuming the default isolation level of Read committed<https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-DB571DA8-864F-4FE9-93B9-3EC2DD0604FE>.

Options include:

· Shorter transactions. This is probably the best/easiest solution if it is possible to change the code.

· Make the SQL run quicker so other transactions have less time to use undo (e.g. is it possible to tune the SQL)

· Make fewer other updates run at the same time to use less undo

· Increasing the size of the undo

I’d note you appear to be using RAC, which I don’t have experience of, and I have only investigated undo enough to fix issues I have experienced myself. I’m not an expert by any stretch, so take what I say with a pinch of salt!

Cheers

PaulH

--
http://www.freelists.org/webpage/oracle-l