# flashback to SCN
# flashback table to timestamp
# flashback to restore point
f0321@DEMO1> create table test as select * from all_objects;
Table created.
# flashback table to timestamp
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> 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;
OBJECT_ID STATUS
---------- -------
3 NEW
5 NEW
2 NEW
4 NEW
f0321@DEMO1> flashback table test to timestamp ( systimestamp - interval '1' minute);
flashback table test to timestamp ( systimestamp - interval '1' minute)
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
f0321@DEMO1> alter table test enable row movement;
Table altered.
f0321@DEMO1> flashback table test to timestamp ( systimestamp - interval '1' minute);
Flashback complete.
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
# flashback to restore point
f0321@DEMO1> create restore point on1;
Restore point created.
f0321@DEMO1> update test set status='VALID' where object_id between 1 and 5;
4 rows updated.
f0321@DEMO1> commit;
Commit complete.
f0321@DEMO1> flashback table test to restore point on1 ;
Flashback complete.
f0321@DEMO1> select object_id, status from test where object_id between 1 and 5;
OBJECT_ID STATUS
---------- -------
3 NEW
5 NEW
2 NEW
4 NEW
# flashback to SCN
f0321@DEMO1> SELECT current_scn, SYSTIMESTAMP FROM v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
5517688 07-MAR-11 06.40.40.644898 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> flashback table test to scn 5517688;
Flashback complete.
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
Hope this helps! Regards Rupam
