Oracle update undo, Massive updates that can generate problems with UNDO tablespace

It is not the first time that I see the need to run a DML which it actually updates many rows, this can be a problem because the UNDO tablespace is not infinite (and not should be).

Recently I had to run an update:

Source   
UPDATE TABLON_TA_ELECT_PILOTO SET CD_PS_TP_VIA = 'STREET' WHERE CD_PS_TP_VIA IS NULL;
commit;

This DML updated nearly 4 million rows, of course UNDO tablespace insufficient.

The solution for this case passed through:

Source   
UPDATE TABLON_TA_ELECT_PILOTO SET CD_PS_TP_VIA = 'STREET' WHERE CD_PS_TP_VIA IS NULL AND rownum<10000;
commit;

The “rownum” pseudo field can be used perfectly in these cases.

Leave a Reply