Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vikram.

Asked: December 09, 2010 - 6:47 am UTC

Last updated: December 10, 2010 - 10:17 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I see there are some changes in table happend in last month.

Can I see, what are changes happend in last month ?


Kind Regards,
Vikram Shinde

and Tom said...

In 11g - there is an option called flashback data archive - that if you enabled would let you see all of the changes that happen in whatever timeframe you wanted.

In 10g - you can use flashback query for short durations (5 day theoretical max in the past) to see changes.

Beyond that, you would/could enable auditing to see who made modifications over time - but not the modifications themselves. That would have to be something you hand coded - you would have to write your own equivalent of 11g's flashback data archive.

http://docs.oracle.com/cd/E11882_01/appdev.112/e17125/adfns_flashback.htm#ADFNS01011

Rating

  (2 ratings)

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

Comments

LogMiner?

Gus Spier, December 09, 2010 - 9:41 pm UTC

Tom, is this a case for LogMiner? It depends on having archived redo logs from last month, of course. It might be a heck of a lot of bother, but it's feasible, right?

Gus
Tom Kyte
December 10, 2010 - 9:55 am UTC

it would be almost impossible to process - it would be a HUGE amount of data for every change, you would have to filter millions or billions of rows.

while technical possible, I do not believe it to be feasible.


(from Middleburg! I'm in Round Hill :) )

OK

Raama, December 10, 2010 - 5:12 am UTC

Hi,
You can try versioning using dbms_wm.enableversioning. Please refer documentation for arguments and usage. This creates a history table named as <table_name>_hist with complete list of DML changes happened for the table.
Tom Kyte
December 10, 2010 - 10:17 am UTC

that actually creates a history VIEW, the history is versioned in the table itself.

I forgot about that - but yes, that would be viable IF you can take the performance hit. Lots of triggers in play and your table constantly gets larger as deletes are turned into updates and updates are turned into an update and insert of a new row. You'd have to be purging old rows manually (using the documented calls in dbms_wm) over time.
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.