0225Snapshot Too Old Error ora-01555.txt

[20150225]Snapshot Too Old Error ora-01555(測試1).txt

--測試的例子來源於Apress.Oracle.Database.Transactions.and.Locking.Revealed.1484207610.pdf

--記錄下來主要是為了以後講解的方便。

create table t as select * from all_objects;
create index t_idx on t(object_name);
exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/u01/app/oracle11g/oradata/test/undotbs02.dbf' SIZE 1M AUTOEXTEND OFF
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

alter system set undo_tablespace = UNDOTBS2;

begin
    for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
                 from t
                where object_name > ' ' )
    loop
        update t
           set object_name = lower(x.object_name)
         where rowid = x.rid;
        if ( mod(x.r,100) = 0 ) then
           commit;
        end if;
   end loop;
   commit;
end;
/

*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 2

This was a very artificial example just to show how it happens in a reliable manner. My UPDATE statement was
generating undo. I had a very small undo tablespace to play with (10MB). I wrapped around in my undo segments
many times, since they are used in a circular fashion. Every time I committed, I allowed Oracle to overwrite the undo
data I generated. Eventually, I needed some piece of data I had generated, but it no longer existed and I received the
ORA-01555 error.

You would be right to point out that in this case, if I had not committed on line 10, I would have received the
following error:

begin
    for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
                 from t
                where object_name > ' ' )
    loop
        update t
           set object_name = lower(x.object_name)
         where rowid = x.rid;
--        if ( mod(x.r,100) = 0 ) then
--           commit;
--        end if;
   end loop;
   commit;
end;
/

begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
ORA-06512: at line 6