Home » SQL & PL/SQL » SQL & PL/SQL » materialize view logs rfresh (oracle 11gr2)
materialize view logs rfresh [message #658076] Fri, 02 December 2016 09:37 Go to next message
guddu_12
Messages: 211
Registered: April 2012
Location: UK
Senior Member
Dear Sir,

I wanted to track changes onto a table so create MV logs and materialize view.

I don't understand how to find changed records once the MV is refreshd.

How can some one track the changed records as don't have any audit column in MV.
Re: materialize view logs rfresh [message #658077 is a reply to message #658076] Fri, 02 December 2016 09:39 Go to previous messageGo to next message
John Watson
Messages: 7699
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that a materialized view is the correct tool for this? I would use a Flashback Data Archive.
Re: materialize view logs rfresh [message #658078 is a reply to message #658077] Fri, 02 December 2016 09:53 Go to previous messageGo to next message
guddu_12
Messages: 211
Registered: April 2012
Location: UK
Senior Member
i WANT TO use for incremental load so my MV logs is on source table where i don't have any timestamp column to track the records, I came to know the MV logs can track but ot sure how to find changes in materialize view
Re: materialize view logs rfresh [message #658079 is a reply to message #658078] Fri, 02 December 2016 10:11 Go to previous messageGo to next message
John Watson
Messages: 7699
Registered: January 2010
Location: Global Village
Senior Member
Perhaps you should use Change Data Capture. If you have Enterprise Edition licences.

By the way, I wish you wouldn't say "record" when you mean "row".
Re: materialize view logs rfresh [message #658080 is a reply to message #658076] Fri, 02 December 2016 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't understand how to find changed records once the MV is refreshd.
Flashback query if you check not long after the refresh (otherwise FDA as John suggested).
Or Log Miner.

Re: materialize view logs rfresh [message #658139 is a reply to message #658080] Mon, 05 December 2016 08:29 Go to previous message
Bill B
Messages: 1821
Registered: December 2004
Senior Member
The way an MVIEW log works is that a pointer to the changed row is put into the log for each MVIEW that references the table. When a refresh of the MVIEW is run, the rows are removed from the MVIEW log as the MVIEW is refreshed.
Previous Topic: Query For N number to top Sales in Region
Next Topic: SYS.DBMS_APPLICATION_INFO.READ_CLIENT_INFO bad result
Goto Forum:
  


Current Time: Wed Dec 12 12:35:36 CST 2018