你在这里

You are here:

flashback query failed with ORA-01555?

Posted by PDSERVICE on 3月 12, 2020 In

if you deleted/updated data by mistake in oracle. and try to recover data from flashback query. Your query may fail with ora-01555 error:

SQL> l
  1  declare
  2  cursor c is select * from testt2 as of scn 5385449;
  3  begin
  4  for i in c loop
  5  null;
  6  end loop;
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 4

if you have no backup , then you can not recover data in this case .

we provide a better flashback service , it can recover part of deleted data . for example .

SQL> set serveroutput on;

SQL> exec better_flashback_table_save('TEST2','TESTT2',2843925,'MYTVSAVE3');

table TEST2.TESTT2 @ scn 2843925 find 5568 rows , copied to TEST2.MYTVSAVE3

PL/SQL procedure successfully completed.

the service step :

1. first expand your undo_retention parameter :

alter system set undo_retention=86400;

expand all undo datafiles

alter database datafile 'undofile' resize BIGGER_SIZE;

the first step avoid undo extent reused by system

2. we will provide procedure better_flashback_table_query , check how many rows can be recovered

3. we will provide procedure better_flashback_table_save , which will recover data and stored in new table .

4. we can also take advantage prm-dul undelete function to help you recover data . pls check https://youtu.be/hIYutqNcVBI

We provide above as a service . contact service@parnassusdata.com