How to disable flashback query logging for a specific table (Oracle)?

Question

We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it would be beneficial to have access to more historical data.

We would like to either restrict logging on our one high activity table. Or disable it completely. I imagine that we may be able to do this by tablespace, I just have not found much on how to make these changes.

Answer 1

You can disable flashback archiving with alter table clause:

alter table YOUR_TABLE_NAME no flashback archive;

It's possible also to limit archive to specified size. To do that you need to create flashback archive designated to this table with desired retention and optionally size quota:

create flashback archive YOUR_TABLE_ARCHIVE tablespace SOME_TABLESPACE quota 512M retention 1 DAY;

Then assign new archive to table:

alter table YOUR_TABLE_NAME flashback archive YOUR_TABLE_ARCHIVE;

Examine Oracle documentation to check additional requirements. E.g. you need FLASHBACK ARCHIVE ADMINSTER privilege to execute statement above.

Answer 2

You can generate scripts for all tables under any schema by executing following query:

SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || 'no flashback archive;' FROM ALL_TABLES WHERE OWNER IN ('YOUR_SCHEMA');