Monday, March 07, 2011

FLASHBACK TABLE


# 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