You don't understand how the 1555 error occurs in the first place. When a commit takes place, the undo segment for that transaction becomes eligible for re-use by another transaction (or even yours). It gets overwritten, boom, you lose the snapshot of what data was in the past. Self-inflicted wound. What is the value of committing every 100000 records when all you need is one commit for the entire transaction (or whatever is going on at that point)?
I totally agree with you in terms of how 01555 occurs.
Table A has 150 Mil rows.
In my scenario, one query with 10 connections (using informatica level partitioning using MOD and ROW_NUM) selecting the data from table A and inserting in table B and committing for 100000 records (again this is also informatica level configurable parameter).
I got 01555 on table A.
I am sure that no other process is doing any DML on table A when my job is running.
Could you please explain me what are the possible scenarios to get 01555 in this case?
Try hard to get what you like OR you will be forced to like what you get.
You can set undo_retention to a higher value (and be prepared for the increase in size for the undo tablespace). Even with that step, if you commit, you just marked that segment as being eligible for use by something/someone else. You just reduce the chance of the error, not eliminate it.