Our application team is running a query which deletes almost 50 million data at a time
Does it carried out at business hours. ?
Why can't you change your logic to carry out in batch wise with few commits, that would help you out - unnecessarily don't hammer the undo.
As you are sure that your application is intended to carry out change.
My Undo retention is 1 hour..
My undo tablespace is of 2.4GB
We can not afford to increase the size of undo tablespace .. If we add a new undo tablespace what will be your action point for that case.. I don't understand the benefit of creating a new one.
Coming back to the last point .. "IT is flush undo data automatically"
If with in UNDO_RETENTION time undo tablespace is filled and my query fails, then if another query is going to do a DML and tries to use UNDO segments then, I think that will also fail because of unavailibility of UNDO SEGMENTS..
Now if after undo_retention time period if again someone asks for another DML which will have to use UNDO segments will the undo tablespace be releasing segments as per the requirements or it will relase all the old and failed undo segments.... ( I think the fist is right.. do correct me if I'm wrong)..