Tuesday, July 20, 2010

How to move flashback data archive to a different tablespace?

The documentation says to ALTER FLASHBACK ARCHIVE REMOVE TABLESPACE following:
If the tablespace to be removed contains any data within the retention period of the flashback archive, then that data will be dropped as well. Therefore, you should move your data to another tablespace before removing the tablespace with this clause.

However, the question is: how do you move the data?

Attempt to move the internal tables stored in flashback archive raises
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_DDL_COLMAP_69926"

And the response of Oracle support is simple:
What you are trying to do is not currently possible. You cannot move the FDA history tables from one tablespace to another, nor can you move the FDA to another tablespace using ALTER FLASHBACK ARCHIVE.

Development however acknowledges that this is a desired feature that they will look into adding for a future release.