Thursday, April 23, 2009

Flashback query

Flashback query relies on data in the UNDO tablespace. If the data has aged out of the undo tablespace, then you will get ORA-01555 snapshot too old.

ORADBA @pd01dwh1> DECLARE
CURSOR c IS select DEFAULTEMAILADDRESS from BKO_SERVER.EMAILDETAILS where action_type_id = 164;
v_rec c%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (32774517554);
OPEN c;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c INTO v_rec;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(v_rec.defaultemailaddress);
END LOOP;
CLOSE c;
END;
/
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$"
too small
ORA-06512: at line 9