April 30, 2010 at 8:37 am
Hello and thanks for your time.
I've been successfully snapshot replicating tables from Oracle 11g to SQL2005 for some time. Recently the need came up and we are trying to replicate a materialized view with no success. The Oracle side has been set for us to see and replicate the view from what I've been told. I was assuming all I would need to do is go to the publication properties and check the new item.
In my publication properties articles section there is only 'tables' listed in the objects to publish. Can someone confirm if there is some setting that I need to change on the SQL side to see other objects (and hopefully views) there?
Thank you for your time.
Eric
April 30, 2010 at 12:51 pm
The Oracle term "materialized view" represents nothing but a table that is updated or refresh in an automatized way.
There is a plain Oracle "table" for each "materialized view"
Questions is...
- How is refresh the materialized view? ... is it transactionally refresh or is it rebuild on demand?
If Oracle side materialized view is rebuild on demand replication should move the whole underlying table - after refresh - to SQL Server side. Research will be needed if materialized view is refresh transactionally.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 4, 2010 at 1:18 pm
eric.winters (4/30/2010)
Hello and thanks for your time.I've been successfully snapshot replicating tables from Oracle 11g to SQL2005 for some time. Recently the need came up and we are trying to replicate a materialized view with no success. The Oracle side has been set for us to see and replicate the view from what I've been told. I was assuming all I would need to do is go to the publication properties and check the new item.
In my publication properties articles section there is only 'tables' listed in the objects to publish. Can someone confirm if there is some setting that I need to change on the SQL side to see other objects (and hopefully views) there?
Thank you for your time.
Eric
Make sure that you can "uncheck" on the publication properties the "view only published objects" (2005) or "show only checked articles in the list" (2008) so that you can actually select it.
* Noel
May 5, 2010 at 8:53 am
PaulB-TheOneAndOnly (4/30/2010)
The Oracle term "materialized view" represents nothing but a table that is updated or refresh in an automatized way.There is a plain Oracle "table" for each "materialized view"
Yes, it sounds like the credential which SQL server is using to access Oracle has simply not been granted select permission on the MV. HTH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply