Flashback – Part 2 (Flashback Query)

Hey team,

This is the second part of our Flashback Tutorial and today we’re gonna talk about FLASHBACK QUERY. Please check here for the first post about Flashback Drop.

Let’s go:

FLASHBACK QUERY

In the last Flashback post, we learnt about restoring tables that were dropped from the database with the RecycleBin facility. But if you think about it, it’s way more likely that a table suffer an undesirable change, than actually be dropped. Example, when you UPDATE a table with values that are not correct, or delete values (and commit, of course), and so on, wouldn’t it be great if we could come back in the past and see how it was before the change? Thanks to the almighty Oracle Database we can! We can use the Flashback Query to see how a table was at a specific time in the past. And the best part of it, is if you are the owner of your table, you can do it yourself, no need to bother the DBA with that (definetely the best part), and you can correct your own mistakes. Also, please keep in mind that for FLASHBACK QUERY to work, we need to have our undo properly configured. To illustrate that, let’s see an example:

Let’s create our same old table:

CREATE TABLE grepora
( column1 VARCHAR2(30),
 column2 VARCHAR2(40),
 column3 VARCHAR2(20) )
  5   TABLESPACE users;

Table created.

Then, let’s insert some values on it:

SQL> insert into grepora values ('value1', 'value2', 'value3');

1 row created.

SQL> insert into grepora values ('line2', 'line2', 'line2');

1 row created.

SQL> insert into grepora values ('line3', 'line3', 'line3');

1 row created.

SQL> insert into grepora values ('line4', 'line4', 'line4');

1 row created.

SQL> insert into grepora values ('line5', 'line5', 'line5');

1 row created.

SQL> commit;

See how the table is at the moment:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

Get the SYSDATE, to know the exact date where you have this amount of data:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> 
SQL> select sysdate from dual;

SYSDATE
-------------------
20/06/2016 16:36:07

Now, let’s make some “mistakes” here, try to change the content of the table, deleting and updating values:

SQL> delete from grepora where column1='line5';

1 row deleted.

SQL> update grepora set column1='line1', column2='line1', column3='line1' where column1='value1';

1 row updated.

SQL> commit;

Commit complete.

And see how the table is right now:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

Check that the content data of the table is different from the original version after our changes. How can we revert that if we didn’t know how it was before that?

We use the famous AS OF TIMESTAMP statement, which allow us to see the table in a different time in the past.

With the example below, check that after using the clause AS OF TIMESTAMP and using the date we caught before to DML our table, we can find the same previous data:

SQL> select * from grepora as of timestamp (to_timestamp('20/06/2016 16:36:07', 'dd/mm/yyyy hh24:mi:ss'));

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

And the current version:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

With this feature, we can see how a table was “before the mistake” and do the proper actions to fix it.

I hope it was clear to everyone, if you have any doubt, please get in touch with GrepOra and we’ll be glad to help.

For the next post, we’ll be doing a test case for FLASHBACK VERSIONS QUERY! Stay Tuned!

Rafael.

 

Advertisements

5 thoughts on “Flashback – Part 2 (Flashback Query)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s