Skip to Main Content
  • Questions
  • Remove History from Flash Back Archive

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arun.

Asked: May 30, 2017 - 11:26 am UTC

Last updated: May 30, 2017 - 1:23 pm UTC

Version: Oracle 12C

Viewed 1000+ times

You Asked

Hi Tom,

I have a requirement to update/Delete(permanent) the archived(Data deleted. But present in flashback archive) data.

Can we update/delete(permanently) a record/set of records present in flashback archive?

and Chris said...

You mean you want to remove rows from Flashback Data Archive that appear when you run:

select * from table as of (timestamp|scn) ...


?

If so, the short answer is no, you can't.

The data in the archive is read only, so you can't selectively remove it. That said, there are a couple of options to wipe old info

Purging data

You can remove all the data in FDA before a given point with:

alter flashback archive <arc> purge before (scn|timestamp) ...


Note there's a bug in this (19206785) so it doesn't work up to 12.1 :( It's fixed in 12.2.

Disabling flashback archive

Purging removes the history for all tables. If you only want to remove one you can by toggling flashback archive on and off:

alter table <tab> no flashback archive;
alter table <tab> flashback archive;


Is this answer out of date? If it is, please let us know via a Comment