OTN Appreciation Day : FlashBack Query

I love OTN, and I love Flashback query. It is a feature looking for an application use-case, but it really can dig you out of a major problem, recovering data you have recently deleted.

Here’s a silly example of the power:

select * from important_data

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Partner A            555-1234
Partner B            555-5678

The choice is made:
update important_data set name = 'Spouse' where name = 'Partner B'

1 row updated.

And the redundant data removed:
delete from important_data where name = 'Partner A'

1 row deleted.

And all is good.
select * from important_data

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Spouse               555-5678

Wait 7... years?
host sleep 7

And the data is missing something?

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Spouse               555-5678

Perhaps we need to reconstruct the deleted data using Flashback
What did the table look like?

select * from important_data AS OF TIMESTAMP (SYSTIMESTAMP - interval '7' second)

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Partner A            555-1234
Partner B            555-5678

And we can use Flashback to re-create the data.
insert into important_data (name,phone_number)
(select 'Hell-eau...', phone_number from important_data AS OF TIMESTAMP (SYSTIMESTAMP - interval '7' second) where name = 'Partner A')

1 row created.

And the data is correct again.

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Hell-eau...          555-1234
Spouse               555-5678

rollback?