Home » Server Options » Replication » Materialised View identifying modifications 10g (Oracle)
Materialised View identifying modifications 10g [message #595037] Thu, 05 September 2013 07:43 Go to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
I am new to Oracle and I inherited an existing database. I have a materialised view on a table that uses a materialised view log. I have been asked to see if it is possible to categorise the rows by change type (inserted, updated or deleted) and populate another materialised view or table accordingly. If this is possible then how can it be achieved.

Thanks in adavnace,
Pat
Re: Materialised View identifying modifications 10g [message #595038 is a reply to message #595037] Thu, 05 September 2013 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (v integer);

Table created.

SQL> create materialized view log on t with rowid;

Materialized view log created.

SQL> desc MLOG$_T  
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 M_ROW$$                                   VARCHAR2(255 CHAR)
 SNAPTIME$$                                DATE
 DMLTYPE$$                                 VARCHAR2(1 CHAR)
 OLD_NEW$$                                 VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$                           RAW(255)

See DMLTYPE$$ column.

Regards
Michel
Re: Materialised View identifying modifications 10g [message #595039 is a reply to message #595038] Thu, 05 September 2013 08:24 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
Thanks for the quick response. I see that by using DMLTYPE$$ it is possible to identify Inserts, Updates and Deletes. Is there any way to interrupt or extend the process/job that refreshes the materilaised view so that it also writes to another table categorising the rows by DMLTYPE$$.
Re: Materialised View identifying modifications 10g [message #595040 is a reply to message #595039] Thu, 05 September 2013 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you mean/want.

Post an example.

Regards
Michel
Re: Materialised View identifying modifications 10g [message #595041 is a reply to message #595040] Thu, 05 September 2013 08:55 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
Hi Michel,

Apologies for any confusion.

My understanding is that a row is inserted into the materialised view log on each insert, update or delete in the master table. We then have a materialised view that is set up to fast refresh. I need to know what process/job/scheduler initiates that fast refresh and wheteher or not it is possible as part of the fast refresh to also populate another table (say tableB) with the modified rows. This other table(tableB) would contain the modified rows which we could then use to do a logical rather than physical delete on another system.

Apologies again if this is not clear,
Pat
Re: Materialised View identifying modifications 10g [message #595042 is a reply to message #595040] Thu, 05 September 2013 09:02 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
So basically, I would like to populate another table with the rows from the materialised view log just prior to the log being purged. Or am I approaching this in the worng way.

Thanks again,
Pat
Re: Materialised View identifying modifications 10g [message #595043 is a reply to message #595042] Thu, 05 September 2013 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create another mview in place of this second table.

Regards
Michel
Re: Materialised View identifying modifications 10g [message #595044 is a reply to message #595043] Thu, 05 September 2013 10:10 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
Ok, so how would I categorise the modifications into Inserts, Updates and Deletes as I thought materialised views were just based on the master table. Or would I create a materialised view on the MLOG$$_T (is this even possible?).
Re: Materialised View identifying modifications 10g [message #595045 is a reply to message #595043] Thu, 05 September 2013 10:14 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
Or would I create a materialised view on the existing materialised view?
Re: Materialised View identifying modifications 10g [message #595046 is a reply to message #595045] Thu, 05 September 2013 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand what you actually want in the end.
Please post an example.

Regards
Michel
Re: Materialised View identifying modifications 10g [message #595047 is a reply to message #595042] Thu, 05 September 2013 11:47 Go to previous messageGo to next message
John Watson
Messages: 4861
Registered: January 2010
Location: Global Village
Senior Member
pclifford147 wrote on Thu, 05 September 2013 15:02
So basically, I would like to populate another table with the rows from the materialised view log just prior to the log being purged. Or am I approaching this in the worng way.

Thanks again,
Pat
It is possible that you could be taking the wrong approach. Do you have Enterprise Edition licences? If so, Change Data Capture might be what you need. It take a while to get your head around, but it does work and is very versatile.
Re: Materialised View identifying modifications 10g [message #595079 is a reply to message #595047] Fri, 06 September 2013 04:49 Go to previous messageGo to next message
pclifford147
Messages: 7
Registered: October 2012
Location: Dublin
Junior Member
Thnaks John, this is what I was looking for now I will see if I can get this implemented.
Re: Materialised View identifying modifications 10g [message #595091 is a reply to message #595079] Fri, 06 September 2013 10:02 Go to previous message
Michel Cadot
Messages: 59996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 05 September 2013 17:56
I still don't understand what you actually want in the end.
Please post an example.

Regards
Michel

Previous Topic: Goldenagte extract issue for 12C database
Next Topic: Materialized View Geometry Column
Goto Forum:
  


Current Time: Sat Dec 20 07:03:58 CST 2014

Total time taken to generate the page: 0.22144 seconds