Skip to Main Content
  • Questions
  • Keep changed data in a separate table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, debayan.

Asked: May 20, 2020 - 8:59 am UTC

Answered by: Chris Saxon - Last updated: May 21, 2020 - 3:07 pm UTC

Category: Database Administration - Version: 12.2

Viewed 100+ times

You Asked

Hi,

Application team have a requirement to keep data changes on particular tables (All dml's) for 4 days to generate reports. Initially I found below 3 options -

1. Create a trigger for DML's on tables which will insert changed data in another table. Drawback of this, is there are millions of DML's and this will make the system slow.
2. Flashback version query- Drawback is this requires huge undo space and queries may fail on production if undo tbs is full.
3. Flashback data archive - This is working for small dml's but for large transactions this is throwing 'ORA-55616: Transaction table needs Flashback Archiver processing' and this error is not resolved yet.

Is there any other solution available in Oracle to meet the requirement?

and we said...

Those are the main methods I'm aware of.

Instead of triggers you could change the application to write the audit information at the same time. But this is likely to take a lot of effort!

Have you spoken to support about the ORA-55616 error?

and you rated our response

  (1 rating)

Reviews

May 21, 2020 - 9:55 am UTC

Reviewer: Debayan

Hi,

Thank you so much for quick response. I am working with support but didn't get any solution till now. Sometimes it takes time to resolve and I will keep working with support. Any suggestion from you on how to troubleshoot the error.
Chris Saxon

Followup  

May 21, 2020 - 3:07 pm UTC

I'm not familiar with that error to be honest. If I can find any further details I'll update this.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.