Track changes on materialized views in Oracle

Question

Hello,

what is the best way to track changes of a materialized views in Oracle. I know, that triggers are available and CDC does not seem to work with materialized views. Are there any clever solutions?

Thanks for your help

Johannes

Answer 1

I assume you are talking about identifying the changes that happen in incrementally-refreshed materialized views. If the MV is in the same database as the underlying tables you can define an extra column, say LAST_UPDATED, in the result set that stores the value of sysdate or systimestamp. If the MV is in a different database you'll have a problem.

I actually just received a patch from Oracle that fixes a bug to allow persisting ROWSCN (and therefore the ROWSCN timestamp) into the remote MV, so this should be an option for distributed MV's. There's a fuzziness of +/- 3 seconds in rowscn timestamps so if you need greater precision you're looking at rolling your own solution.

Answer 2

You could probably query the MV log itself, if present:

http://www.sqlsnippets.com/en/topic-12878.html