Solved

How do I modify the materialized view log table to include fields from the amster table

Posted on 2009-07-08
8
566 Views
Last Modified: 2013-12-19
I created a log table for the master table to be used to refresh data on a materialized view. However, I need the table to record information from 2 additional fields in the master table. How would I configure the log file to do this?
0
Comment
Question by:sikyala
  • 4
  • 4
8 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24804915
The materialized view log is just to record primary key or rowids of the changed rows. It is not required to have any other columns in the mview log.
0
 

Author Comment

by:sikyala
ID: 24805027
I had to change the primary key on the master table based on the new database system developed that will soon be deployed. I had created the materialized views based on the old primary key which the client used to update their indexes. The clients indexes are based on the old primary key. They want to be able to query based on the old primary key which no longer exist in the log table. This is why I am trying to see if the log table can be modified to include the old primary key fields.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 500 total points
ID: 24805081
In this case I have never tried what you ask. I have always completely recreated my materialized view logs if I changed the primary key structure. I'm sorry, I am not sure if what you want to do is possible or advisable. Can't you just recreate the remote MVIEWS completely with a refresh of the master?
0
 

Author Comment

by:sikyala
ID: 24805351
yes I have done that because the mviews became invalid when I changed the primary key
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24882539
Looking back at the quesiton I may have been in error when I said it is not required to include columns. This is the command I use on my mview logs.

create materialized view log on scott.REP_TEST tablespace data with primary key including new values
/

So there is no column specification.

But it is possible to use the WITH() clause (without using primary key clause) to specify additional columns, such as join columns.

0
 

Author Comment

by:sikyala
ID: 24882604
do you know the syntax for using the with clause?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24882661
0
 

Author Comment

by:sikyala
ID: 24882814
thanks it worked.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now