Today we are going to discuss about FLASHBACK TABLE. As usual, first, I am tagging here the previous posts about Flashback Technology, so feel free to check it out if you want:
Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)
So let’s do it people.
Flashback Table is a very interesting facility that our almighty Oracle Database provide us, giving us the ease of flashback a table (obviously) to a point-in-time in the past or even to an SCN.
An interest part is: If you have dependent values on this table, they will be reverted as well when you perform the flashback table! Awesome right?
The difference, comparing to all other previous sections until now is: All of them, did not affect the table as a whole, it was very punctual. Now we have the possibility to go back with the entire table with only one simple command.
The use of flashback table is a MUCH QUICKER, SIMPLER and INDEPENDENT option to recover a table to a previous position, comparing to an incomplete recover for example. Why independent? Because if you are having a bad day, and do something wrong , you can flashback your own table quickly, bothering nobody.
There is an important point to be mentioned:
- All triggers are disabled when you perform Flashback Table operation, and they remain disable regardless they were enabled or disabled. So make sure to identify the enabled ones before to execute the Flashback Table.
Steps to perform a Flashback Table:
- Enable Row Movement on the table you desire to perform the flashback
- Get an SCN or Timestamp to go back in time (I wish I could do it with my life sometimes).
- Check the current values on your table.
- Do some changes on it.
- Check the table with the wrong values.
- Flashback the table to the SCN or Timestamp you caught at the step 2.
- Check that everything is as expected on your table.
So let’s do a real example here:
SQL> alter table grepora enable row movement; Table altered.
SQL> @scn 2503726817930
SQL> select * from grepora; COLUMN COLUMN COLUMN ------ ------ ------ line1 line1 line1 line2 line2 line2 line3 line3 line3 line4 line4 line4 line5 line5 line5
SQL> update grepora set column1='grepora'; 5 rows updated. SQL> commit; Commit complete.
SQL> select * from grepora; COLUMN1 COLUMN COLUMN -------- ------ ------ grepora line1 line1 grepora line2 line2 grepora line3 line3 grepora line4 line4 grepora line5 line5
SQL> flashback table grepora to scn 2503726817930; Flashback complete.
SQL> select * from grepora; COLUMN1 COLUMN COLUMN -------- ------ ------ line1 line1 line1 line2 line2 line2 line3 line3 line3 line4 line4 line4 line5 line5 line5
PS: If you want to already enable the triggers along with the flashback command, please use:
flashback table grepora to scn 2503726817930 enable triggers;
And there we go! The table is reverted to its previous position. For this time we used SCN to flashback.
Also bear in mind that as most of the Flashback operations, this one is also undo-based, so make sure you have the size and retention that you need.
Please feel free to comment and e-mail us in case of any doubt or suggestion.
Have a wonderful week.