28 | 03 | 2024
Latest Articles
Popular Articles

Administration

Flashback Table to a previous state

User Rating:  / 0
PoorBest 

Flashback Table to a previous state



With Flashback Query we query the database as it was in the (recent) past.
With Flashback Table we revert DML done a a table, a couple of tables ( think about constraints ) assuming this DML occurred in the recent past.

Flashback Table is similar to Flashback Query, we can use the TO TIMESTAMP or the TO SCN syntax.

Consider the same test scenario as we used for Flashback Query and assume there is another update for the (€,$) exchange rate.



SQL> update t_cur_rate set exch_rate=1.2323,exch_time=systimestamp where currency_1='€' and currency_2='$';

1 row updated.

SQL> commit;

Commit complete.



In between other databases changes go on, and then we suddenly realize that the most recent exhangre rate is an error, we would like to revert it back to the previous exchange rate without affecting the other database changes. We can do this with the Flashback Table technology.


STEP 1 : What is the actual exchange rate ?

 

SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2323 20-SEP-10 08.52.46.841942 PM      9165337



STEP 2 : What was the exchange rate prior to the incorrect update ?


SQL> begin
2  dbms_flashback.enable_at_system_change_number(9165300);
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2989 19-SEP-10 10.18.53.140120 PM      9162115

 



STEP 3 : We try to revert to the old exchange rate, but we can' t as long as we haven' t enabled row movement for that table. Also we can' t enable row movement for a table while we are in flashback query mode.

 

SQL> flashback table t_cur_rate to SCN 9162115;
flashback table t_cur_rate to SCN 9162115
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table t_cur_rate enable row movement;
alter table t_cur_rate enable row movement
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode


SQL> begin
2  dbms_flashback.disable;
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> alter table t_cur_rate enable row movement;

Table altered.

 

See also PL/SQL invalidations when you enable row movement at table level


STEP 4 : Once row movement is enabled we can flashback the table to its previous state using the TO SCN attribute.


SQL> flashback table t_cur_rate to SCN 9162115;

Flashback complete.

SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2989 19-SEP-10 10.18.53.140120 PM      9166964



STEP 5 : We flashback the flashback table operation, and do a similar operation using the TO TIMESTAMP attribute.

 


SQL> flashback table t_cur_rate to SCN 9165337;

Flashback complete.

SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2323 20-SEP-10 08.52.46.841942 PM      9167192


SQL> flashback table t_cur_rate to TIMESTAMP to_timestamp('19-SEP-10 10.20.00.000000 PM');

Flashback complete.

SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2989 19-SEP-10 10.18.53.140120 PM      9167424

 


Note that you can flashback multiple tables in 1 operation, this is interesting for parent child tables.
Note also that by default triggers are disabled during the flashback table operation.