重制ORA-01555錯誤

實驗步驟如下：

create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ocp/undotbs2.dbf' size 10M;

alter system set undo_tablespace=undotbs2;

alter system set undo_retention=2 scope=both;

第1步、session1: 目标是讓b表報快照過舊的報錯

conn gyj/gyj

create table a (id int,cc varchar2(10));

insert into a values(1,'hello');

commit;

create table b(id int,cc varchar2(10));

insert into b values(10,'AAAAAA');

select * from a;

select * from b;

var x refcursor;

exec open :x for select * from b;

第2步、session2:修改b表,字段cc前鏡像"OK"儲存在UDNO段中

update b set cc='BBBBBB' where id= 10;

第3步、session 3:該條語句就是重新整理緩存

conn / as sysdba

SQL> alter session set events = 'immediate trace name flush_cache'; --9i提供強制刷緩存

(alter system flush buffer_cache;--10g提供的一種刷緩存方法)

第4步、 session2: 在A表上行大的事務,多運作幾次以確定，復原段被覆寫

begin

for i in 1..20000 loop

update a set cc='HELLOWWWW';

end loop;

end;

/

第5步、session 1:在B表上執行查詢(第一步的查詢)

SQL> print :x

ERROR:

ORA-01555: snapshot too old: rollback segment number 21 with name "_SYSSMU21$" too small