Category: Database

Hi all,

If you have missed the previous Flashback posts, please go through these links to find it and read them if you feel like!

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)

And now, we are half way there to the end of the Flashback posts, let’s see a little more about FLASHBACK TRANSACTION QUERY.

More“Flashback – Part 4 (Flashback Transaction Query)”

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)

 

Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

More“Flashback- Part 3 (Flashback Versions Query)”

Hey team,

This is the second part of our Flashback Tutorial and today we’re gonna talk about FLASHBACK QUERY. Please check below for the full serie:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)

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.