Here is just a reminder what is Oracle Flashback in general:
Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Here are the steps which we need to accomplish if want to have table enabled for flashback. Lets take as an example Employees table from Oracle demo schema HR. Assume, that we do not have flashback data archive created in database yet.
- Connect as system user and execute following statements:
- Create a tablespace which will be dedicated to store flashback archive
create tablespace fda_tbs datafile 'fda_tbs.dat' size 5g online;
-
- Grant user HR to use recently created tablespace
alter user hr quota 5g on fda_tbs;
-
- Grant flashback archive administration privilege to user HR
grant flashback archive administer to hr;
- Connect as user HR
- Create flashback data archive
create flashback archive fda_archive tablespace fda_tbs retention 1 year;
-
- Enable flashback for particular table – Employees
alter table employees flashback archive fda_archive;
-
- In case you need to disable flashback for particular table
alter table employees no flashback archive;
Other flashback management commands
- Purge flashback archive
alter flashback archive fda_archive purge all;
- Modify flashback archive retention period
alter flashback archive fda_archive modify retention 10 day;
- Drop flashback archive
drop flashback archive fda_archive;
Some useful SQL statements
- Tables which are enabled for flashback
select * from dba_flashback_archive_tables
- Flashback archive and tablespace mapping
select * from dba_flashback_archive_ts;
- List of flashback archives
select * from dba_flashback_archive;
- Select data versions for particular table in spacified time range
SELECT versions_operation, versions_starttime,versions_endtime, t.* FROM employees
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2018-03-01 09:20:00', 'YYYY-MM-DD HH24:MI:SS') AND systimestamp t