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

Last updated: May 21, 2020 - 3:07 pm UTC

Version: 12.2

Viewed 1000+ 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 Chris 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?

Rating

  (1 rating)

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

Comments

Debayan, May 21, 2020 - 9:55 am UTC

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
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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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