Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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

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
sikyala
Asked:
sikyala
  • 4
  • 4
2 Solutions
 
mrjoltcolaCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
mrjoltcolaCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
sikyalaSenior Database AdministratorAuthor Commented:
yes I have done that because the mviews became invalid when I changed the primary key
0
 
mrjoltcolaCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
do you know the syntax for using the with clause?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
thanks it worked.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now