Oracle for Beginner: 'Snapshot too old' a simple practice

In Oracle, there is a undo tablespace (is controlled by undo_tablespace parameter) that is used by oracle to store old image of updated rows. With this tablespace, you can rollback to cancel the transaction. In a undo tablespace, there are some undo segments that is controlled by oracle automatically (it is recommended that undo_management init parameter has value AUTO). For each old row images will be retained at undo segment for 900 seconds (undo_retention parameter), after that the block for old images can be overwritten by another old row images. If the undo tablespace is too small to retain old row images, sometime you will get an error that will tell you about there is no enough space for undo data. Here is an example of that error:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_2'

The "snapshot too old" error can be happen if some old row images, that is used by another query for read consistency, have been overwritten. In this article, from another resources too, I would like to share about a simple practice to help us to understand about this error.

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create undo tablespace undotbs_2 datafile size 1M;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS_2';

System altered.

SQL> create user budsus identified by oracle quota 10M on users;
SQL> grant connect, create table, create any index to budsus;

Grant succeeded.
SQL> conn budsus/oracle
Connected.
SQL> create table siswa (id number(5), nama varchar(100));

SQL> create table dump(tmp varchar(100));

SQL> begin
2 for i in 1..5000 loop
3 insert into siswa values (i, 'siswa terbaru ' || i);
4 if mod(i, 100) = 0 then
5 insert into dump values ('teeeeeeeemmmmmmmmmppppppppp');
6 commit;
7 end if;
8 end loop;
9 commit;
10 end;

SQL> declare
2 cursor crs_siswa is select rowid, siswa.* from siswa where id < 250;
3 begin
4 for v_siswa in crs_siswa loop
5 update dump set tmp = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
6 update dump set tmp = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
7 update dump set tmp = 'ccccccccccccccccccccccccccccc';
8 update siswa set nama = 'siswa terupdate ' || v_siswa.id where v_siswa.rowid = rowid;
9 commit;
10 end loop;
11 end;
12 /
...
ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small

SQL> conn / as sysdba;
SQL> alter system set undo_tablespace='UNDOTBS1';
SQL> drop tablespace undotbs_2;
SQL> drop table siswa;
SQL> drop table dump;
SQL> quit

Thanks.