Flashback - Travel Back In Time

Need to reset the database or individual objects to a specific point in time? Use flashback! This comes in very handy when testing database migrations, for example. The script contains examples for the most useful functions.

-- =============================================================================
-- flashback database
-- =============================================================================
-- check if flashback database is on
select flashback_on
from   v$database
;
-- check flashback configuration
select *
from   v$parameter
where  name like 'db_recovery%'
or     name like '%flashback%'
;
--db_recovery_file_dest          -> flashback recovery area (location)
-- - ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
--db_recovery_file_dest_size     -> hard limit on filesystem size (bytes)
--db_flashback_retention_target  -> default 1440 min (1 day)
--
-- estimated_flashback_size: an estimate of space required to meet retention target
-- oldest_flashback_scn/time: earliest point in time the database can be set back to
select *
from   v$flashback_database_log
;
-- i/o statistics
select *
from   v$flashback_database_stat
;
select current_scn
from   v$database
;
-- set flashback on (for flashback database), when mounted but not open
shutdown immediate; -- do not abort!
startup mount;
alter database flashback on; -- flashback needs to be configured
alter database open;
--
-- flashback database
shutdown immediate;
startup mount;
flashback database to scn 8575371543070; -- flashback to (optionally before) scn or timestamp
alter database open resetlogs;
--
-- =============================================================================
-- flashback table, version and transaction query
-- =============================================================================
-- (from undo, as long as undo_retention seconds)
-- - flashback version query
select salary
from   employees
where  employee_id = 100
;
update employees
set    salary = salary * 1.3
where  employee_id = 100
;
update departments
set    department_name = department_name||'U'
;
commit
;
select versions_starttime
,      versions_startscn
,      versions_endtime
,      versions_endscn
,      rawtohex(versions_xid)
,      salary
from   employees
       versions between scn minvalue and maxvalue
where  employee_id = 100
;
-- get all actions of a transaction (flashback transaction query)
select *
from   flashback_transaction_query
where  xid = hextoraw('02001D008E010000')
;
select *
from   flashback_transaction_query
where  commit_scn = 1299829
;
-- other flashback version query settings
  VERSIONS BETWEEN TIMESTAMP
    SYSTIMESTAMP - INTERVAL '10' MINUTE AND
    SYSTIMESTAMP - INTERVAL '1' MINUTE
;
select *
from   employees
       as of timestamp (systimestamp - interval '20' minute)
where  employee_id = 100
;
-- flashback table...
create table ev as
select * from employees
;
drop table ev;
flashback table ev to before drop rename to ev_recovered;
select * from ev_recovered;
desc ev;
-- flashback to point in time or scn: requires flashback object privilege (and row movement)
alter table departments enable row movement;
select * from departments;
flashback table departments to timestamp (systimestamp - interval '20' minute);
select * from departments;