ORA-01555 snapshot_too_old quick demo

script_snapshot_too_old_demo.sql

spool spool_snapshot_too_old_demo.log

set echo on time off timi on serverout on

drop table snapshot_too_old_demo purge;

create table snapshot_too_old_demo as select * from dual;

create undo tablespace tiny_undotbs2
    datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/tiny_undotbs2.dbf'
    size 500k
/

alter system set undo_tablespace = tiny_undotbs2;

var cur refcursor

exec open :cur for select * from snapshot_too_old_demo;

begin 
    for i in 1 .. 1e4 loop
      update snapshot_too_old_demo 
      set dummy = to_char(mod(i, 10));
      commit;
    end loop;
end;
/

declare
    result snapshot_too_old_demo.dummy%type;
begin 
    fetch :cur into result;
    dbms_output.put_line(result);
end;
/

alter system set undo_tablespace = undotbs2;

drop tablespace tiny_undotbs2 including contents and datafiles;

exit

spool_snapshot_too_old_demo.log

SQL> 
SQL> drop table snapshot_too_old_demo purge;

Table dropped.

Elapsed: 00:00:00.02
SQL> 
SQL> create table snapshot_too_old_demo as select * from dual;

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> create undo tablespace tiny_undotbs2
  2      datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/tiny_undotbs2.dbf'
  3      size 500k
  4  /

Tablespace created.

Elapsed: 00:00:00.05
SQL> 
SQL> alter system set undo_tablespace = tiny_undotbs2;

System altered.

Elapsed: 00:00:00.01
SQL> 
SQL> var cur refcursor
SQL> 
SQL> exec open :cur for select * from snapshot_too_old_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 
SQL> begin
  2      for i in 1 .. 1e4 loop
  3        update snapshot_too_old_demo
  4        set dummy = to_char(mod(i, 10));
  5        commit;
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.98
SQL> 
SQL> declare
  2      result snapshot_too_old_demo.dummy%type;
  3  begin
  4      fetch :cur into result;
  5      dbms_output.put_line(result);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 6 with name
"_SYSSMU6_1244306738$" too small
ORA-06512: at line 4


Elapsed: 00:00:00.01
SQL> 
SQL> alter system set undo_tablespace = undotbs2;

System altered.

Elapsed: 00:00:00.01
SQL> 
SQL> drop tablespace tiny_undotbs2 including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:00.08
SQL> 
SQL> exit