Wednesday, December 23, 2009

Using Flashback Archive to avoid ORA-01555

SQL> create table test as select object_id from dba_objects;

Table created.

SQL> create table fla_test as select object_id from dba_objects;

Table created.

SQL> create flashback archive default fla tablespace users quota 1G retention 1 year;

Flashback archive created.

SQL> alter table a.fla_test flashback archive fla;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5936742

SQL> begin
for i in 1..10000 loop
update test set object_id = object_id where object_id = 20;
update fla_test set object_id = object_id where object_id = 20;
commit;
end loop;
end;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL> select object_id from test as of scn 5936742 where object_id = 20;
select object_id from test as of scn 5936742 where object_id = 20
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 6 with name
"_SYSSMU6_2443381498$" too small


SQL> select object_id from fla_test as of scn 5936742 where object_id = 20;

OBJECT_ID
----------
20