Oracle flashback query

To select data as if we where in the past, you can use a flashback query. How long you can go back depends on the size of the UNDO tablespace and on how quickly that fills up.

If you want to see the content of the table 1 hour ago you can query it like:

SELECT *
FROM tablename
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;If you want to see the content of the table 2 days ago you can query it like:

SELECT *
FROM tablename
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '2' DAY;or you can select the status per SCN number.
To query the current SCN number:

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL; 

To select a SCN number (System Change Number) on a specific timestamp:

SELECT TIMESTAMP_TO_SCN('11-JUN-09 10.30.09.000000000 AM') FROM dual; 



With this SCN number you can query in the past with:

SELECT *
FROM tablename
AS OF SCN scn-number;


To select changes on a table since a specific System Change Numbers (SCN):

SELECT * FROM tabelnaam
VERSIONS BETWEEN SCN SCN_x AND MAXVALUE;