# flashback query as of timestamp
# flashback query as of scn
# flashback query as of scn
f0321@DEMO1> select object_id, status from test where object_id between 1 and 5;
OBJECT_ID STATUS
---------- -------
3 VALID
5 VALID
2 VALID
4 VALID
f0321@DEMO1> SELECT current_scn, SYSTIMESTAMP FROM v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
5521836 07-MAR-11 07.40.11.325496 PM -05:00
f0321@DEMO1> update test set status='NEW' where object_id between 1 and 5;
4 rows updated.
f0321@DEMO1> commit;
Commit complete.
f0321@DEMO1> select object_id, status from test where object_id between 1 and 5 as of scn 5521836;
select object_id, status from test where object_id between 1 and 5 as of scn 5521836
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
f0321@DEMO1> select object_id, status from test as of scn 5521836 where object_id between 1 and 5;
OBJECT_ID STATUS
---------- -------
3 VALID
5 VALID
2 VALID
4 VALID
# flashback query as of timestamp
select object_id, status from test as of timestamp
to_timestamp('2011-03-07 19:52:00', 'YYYY-MM-DD HH24:MI:SS')
where object_id between 1 and 5
/
Hope this helps! Regards Rupam
