Flashback Table

Flashback Table
=============

Oracle's Flashback table feature restores the state of rows in a table as of point of time in past. Flashing back the tables also restore the indexes, triggers and constraints.
The table flashback is depened on the undo tablespace retention and hence the recovery window for the table is very small, you should be quick to perform this.

Requirement to enable Flashback Table:
=============================
1.) Undo Tablespace window will be enough to flashback.
2.) Row movement must be enabled.
3.) Non system user must have flashback any table privilege or flashback object privilege on specific table.
4.) By default, user can be able to flashback his/her schema tables.

Restriciton's on Flashback Table:
========================
1.) Flashback table can't be possible on system, x$tables and remote tables.
2.) No DDL alteration can be flashbacked such as modifying columns in table.
3.) The statistics on the table can not be flashedback.
4.) Truncate (No undo generation -> perform TSPITR or database flashback or use exp/imp by restoring from backup)

DEMO

======
I am using my test machine for demonstration purpose and the <fla_bak> table into my <ext> schema,

SQL> show parameter undo
NAME                          VALUE
------------------------      -----------------
undo_management          AUTO
undo_retention                1800
undo_tablespace              UNDOTBS1

1.) Row movement not enabled.

SQL> create table fla_bak as select * from fla_test;
Table created.
SQL> select count(*) from fla_bak;
  COUNT(*)
----------
 19
SQL> delete from fla_bak where min_salary >= 5500;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table fla_bak to timestamp systimestamp - interval '1' minute;
flashback table fla_bak to timestamp systimestamp - interval '1' minute
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

2.) Row movement enabled.

SQL> alter table fla_bak enable row movement;
Table altered.
SQL> flashback table fla_bak to timestamp systimestamp - interval '1' minute;
Flashback complete.
SQL> select count(*) from fla_bak;
  COUNT(*)
----------
 10
(I tried immediately but passes the 1 minute and returned only 10 rows)

another try...
SQL> flashback table fla_bak to timestamp systimestamp - interval '4' minute;
flashback table fla_bak to timestamp systimestamp - interval '4' minute
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

another try...
SQL> flashback table fla_bak to timestamp systimestamp - interval '3' minute;
Flashback complete.
SQL> select count(*) from fla_bak;
  COUNT(*)
----------
 19

Conclusion:: Bingo..flashback succeeded,

TEST!TEST!TEST