[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
