ORA-01555是因为回滚段中的原数据镜像被覆盖，一致性读时查询时不到。
模拟ORA-01555错误 ：
1.配置一个不能自动扩展的UNDO表空间
这里要提前
创建一个UNDO表空间，大小5M并且表空间不支持自动扩展。--为方便实验当然可以设置更小的表空间比如1M 2M等。
所需语句是：create undo tablespace test1_undo datafile '/u01/app/oracle/oradata/bys001/test1_undo.dbf' size 5m;
--默认不启用自动扩展，通过select file_name,autoextensible from dba_data_files;可以查看到。删除表空间用： drop tablespace test1_undo including contents and datafiles;
然后切换当
前UNDO表空间为新创建的，语句alter system set undo_tablespace=test1_undo;
最终是如下状态：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string TEST1_UNDO
SQL> select name,value/1024 as KB from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME KB
---------------------------------------------------------------- ----------
redo size 1.75
undo change vector size 0.18359375
SQL> col tablespace_name for a15
SQL> col file_id for a5
SQL> col file_name for a30
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files where file_name like '%test1_undo%';
FILE_ FILE_NAME TABLESPACE_NAME MB
----- ------------------------------ --------------- ----------
7 /u01/app/oracle/oradata/bys001 TEST1_UNDO 5
/test1_undo.dbf
SQL> set time on
11:16:58 SQL> drop table test;
Table dropped
2.建表并执行循环删除的脚本
11:17:38 SQL> create table test as select * from dba_objects;
Table created
11:18:33 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1556131
11:19:16 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1556132
11:19:21 SQL> select count(*) from test;
COUNT(*)
----------
72795
11:19:37 SQL>
11:19:55 SQL> begin
2 for i in 1 .. 20000 loop
3 delete from test where rownum < 3;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed
11:20:30 SQL> select count(*) from test;
COUNT(*)
----------
32795
3.使用as of scn方式 查询之前的数据，出现ORA-01555
11:21:29 SQL> select count(*) from test
as of scn 1556132;
select count(*) from test as of scn 1556132
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_425896126$" too small
11:21:43 SQL> select count(*) from test
as of timestamp to_timestamp('2013/06/24 11:19:00','yyyy/mm/dd hh24:mi:ss');
select count(*) from test as of timestamp to_timestamp('2013/06/24 11:19:00','yyyy/mm/dd hh24:mi:ss')
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_425896126$" too small
附实验用到的一个小脚本：
begin
for i in 1 .. 20000 loop
delete from test where rownum < 3;
commit;
end loop;
end;
/
