Cannot drop Oracle flashback data archive

Question

Wanted to do a clean test on Flashback Data Archive so had to remove the existing.

I disabled FDA on all enabled tables and dropped FDA.

But still i see the record in DBA_FLASHBACK_ARCHIVE And DBA_FLASHBACK_ARCHIVE_TS

If i attempt to drop the tablespace, i get "ora-55641 cannot drop tablespace used by flashback data archive". When i drop FDA its successful and no errors.

I tried alter flashback data archive purge and i still could not drop TS

alter flashback archive fla1 purge all;

Then i tried to alter FDA and see if i can delink - alter flashback archive fla1 remove tablespace tbs2; I get the error "ORA-55626: Cannot remove the Flashback Archive's primary tablespace"

Can i know if any internal tables has to be cleaned.

Answer 1

I had a similar situation; in my case I had been having some issues with Flashback Data Archive (Also Known as FBA, FBDA, FDA) with some queries, possibly due to materialized views. So to solve that problem, I dis-associated those tables from the FBDA, using DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA(owner_name, table_name) which solved the performance problem, but of course those tables were no longer tracked.

Now, I've given up on the FBA on that instance for now, and when cleaning up, I hit the same ORA-55641 and also ORA-55626 when trying to clean up.

What I ended up doing was to re-associate the tables to the flashback data archive, and also (not sure if this was needed, but ...) I purged all records:

alter flashback archive ARCH_FLASHBACK_10_YEAR purge all;

-- Identify tables tied to the flashback archive; mine was named ARCH_FLASHBACK_10_YEAR 
select owner_name, table_name, FLASHBACK_ARCHIVE_NAME, 
    ARCHIVE_TABLE_NAME, status from DBA_FLASHBACK_ARCHIVE_TABLES;

-- look for ones with a STATUS of "DISASSOCIATED" and do the next two 
-- statements for those tables

exec DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA ('YOUR_OWNER', 'YOUR_TABLE')
alter table YOUR_OWNER.YOUR_TABLE no flashback archive;

-- Then query again.  When clean:

alter flashback archive ARCH_FLASHBACK_10_YEAR remove  tablespace ARCH_HIST;
drop  flashback archive ARCH_FLASHBACK_10_YEAR;

-- And if ARCH_HIST has no other data:
drop tablespace ARCH_HIST including contents and datafiles;

And after doing those steps I was able to drop the flashback archive, and drop the tablespace associated with that flashback archive.