OTN Appreciation Day : FlashBack Query
11/10/2016 Leave a comment
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?