Flashback – Part 5 (Flashback Table)

Hi everybody,

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:

1. Enable Row Movement on the table you desire to perform the flashback
2. Get an SCN or Timestamp to go back in time (I wish I could do it with my life sometimes).
3. Check the current values on your table.
4. Do some changes on it.
5. Check the table with the wrong values.
6. Flashback the table to the SCN or Timestamp you caught at the step 2.
7. Check that everything is as expected on your table.

So let’s do a real example here:

Step 1:

SQL> alter table grepora enable row movement;

Table altered.

Step 2:

SQL> @scn
   2503726817930

Step 3:

SQL> select * from grepora;
COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

Step 4:

SQL> update grepora set column1='grepora';

5 rows updated.

SQL> commit;

Commit complete.

Step 5:

SQL> select * from grepora;
COLUMN1  COLUMN COLUMN
-------- ------ ------
grepora  line1 line1
grepora  line2 line2
grepora  line3 line3
grepora  line4 line4
grepora  line5 line5

Step 6:

SQL> flashback table grepora to scn 2503726817930;

Flashback complete.

Step 7:

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.

Rafael.