To introduce the second kind of flashback, let me first present you a seemingly trivial scenario: Open one session against your Oracle Database, take a table of your choice and make an update of one row of it without commit. Then open a second session and select the rows of that table.

Please notice two things:  1. The query does not wait for the first session to commit. 2. The query shows a consistent result set with the old value of the row that you updated in the first session. This is called read consistency and is delivered by Oracle Databases since version 6 without the need for the select to lock rows in the table. We make that possible by retaining the old value of the updated row (called before image)  in undo segments. Since the 9i version, we make additional usage of these before images; in 9i you can do a flashback query like this:

select * from t as of timestamp systimestamp – interval ’15‘ minute;

That select shows the content of table t as it was before 15 minutes – provided that the before images to reconstruct this content are still present in the undo segments. 10g takes that approach one step further by making it possible to do a „table point in time recovery“ with the old values of the table reconstructed from the before images. The command goes

alter table t enable row movement;

flashback table t to timestamp systimestamp – interval ’15‘ minute;

You can also provide a comma separated list of tables in that statement to flashback all the tables that belong together. This can be extremely useful and works very fast and easy compared to the old cumbersome and time consuming methods to achieve the same result!

If you just want to investigate who made exactly what kind of logical mistake, you can achieve that with Flashback Transaction Query – also a 10g New Feature and explained here.

So what is that Flashback stuff all about? Generally, flashback comes into play if some kind of logical mistake occured, that you would like to correct – or at least find out what exactly happend. We have 3 kinds of flashback that only have that name in common while basing on completely other internal architecture.

1) flashback table to before drop

2) flashback query, flashback transaction query, flashback table to timestamp

3) flashback database

Today, we will look at 1)

If you have an Oracle Database of version 10g or higher, then by default tables are not dropped by the command

drop table t;

Instead, they get renamed to something like BIN$YO4ieOIZ5WngQKAK4QstLg==$0.

You can see that in SQL*Plus with the command show recyclebin or with select * from user_recyclebin;

As long as the extents of this renamed table are not being reused by some other segment that needs space in the datafile where the extents of  the „dropped“ table reside, you can get that table back with the command

flashback table t to before drop;

This feature comes „for free“ and has nothing to do with and no need for flashback logs that are invoked by the command alter database flashback on;