I am using 11.2.0.3 database . I have confusion about using commit to resolve ora-30036.
I have one- time migration script that has many updates and I am getting ORA-30036 ( unable to extend segment by 4 in undo tablespace 'UNDOTBS') .
Our undo tablespace in NOT in autoextend mode and as this is just a one time event we do not want increase it. It is currently 6 GB.
Would frequent commit resolve ORA-30036 in my case? If not why? Are there any better solution to resolve it.
When I say frequent commit , I do not mean commiting after each row , I just want to commit after may be million rows so that my active undo extend will turn into unexpired undo extend and Oracle will be able to use it in the event of space pressure.
Note:
undo_management : AUTO
undo_retention : 2700
Retention of undo tablespace is NOGAURANTEE.