Re: mview over private database link

  • From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
  • To: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>
  • Date: Fri, 7 Oct 2011 14:29:42 +0200


Joel, I am certainly able to solve it through 2 ways (which work):
1- to create on TARGET another private link (which drawback is it
multiplies their number and more schemas are able to work on remote db
with some extended privs I 'd like to avoid - of course can do that by
another schema on SRC side with limited privs)
2- to create an mview not within TARGET schema, but in the MIDDLE one
and grant proper privileges (which seems better than solution 1)

I just wonder if it is possible to do it the way I provided here as it
would be more like encapsulating data within 'independent' layers. Why I
ask is that calling simple view with provided grants works properly,
while trying to create mview apparently not - I would like to understand
why not or find out what I do wrong in order to make it work.
So I am not really searching for any solution but the one which would be
consistent with the spirit of what I wrote above

TIA, Remigiusz

W dniu 07.10.2011 14:04, Joel.Patterson@xxxxxxxxxxx pisze:
> Obviously if you are able to create an MV on a view, then it can happen.  I 
> did not look into this.  I'm just speaking off the top of my head -- that to 
> know about a change, then you need to know which record... but a view is just 
> a sql statement stored somewhere - to which you are doing a 'complete' 
> refresh -- thus taking everything and therefore do not need that info.
> But this scenario begs the question in my mind -- why not just create a view 
> that selects across the database link?  I suppose you could do it again as 
> well...  Since at first thought it seems simpler than a MV that does complete 
> refreshes..., creates a table, a MV, and possibly a job to schedule the 
> refresh.   Since that is rhetorical in a way, I'll jump in and ask if it is 
> because the data is static most of the day, and therefore once loaded the 
> performance is better...
> I'm not trying to answer anything here.
> Joel Patterson
> Database Administrator
> 904 727-2546
> -----Original Message-----
> From: Remigiusz Sokolowski [mailto:remigiusz.sokolowski@xxxxxxxxxx] 
> Sent: Friday, October 07, 2011 2:02 AM
> To: Patterson, Joel
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: mview over private database link
> I thought the mview log is a must if I need a fast refresh and here I
> need simple complete one.
> W dniu 06.10.2011 20:26, Joel.Patterson@xxxxxxxxxxx pisze:
>> I could be off, but maybe it will point to something.  The reason being is 
>> that I have never created a MV on a view.  Doesn't there need to be a MV log 
>> on src?
>> In my mind I see a table on src.   And a MV log on src for that table.   
>> Then I create a MV on Middle selecting from table@src on say primary key 
>> (verses rowid).
>> When querying middle, I would see a MV, a Table also, a dbms_job, (at 
>> least).   So, now if I wish to create a MV on middle.'table', I would repeat 
>> the scenario.
>> ....   hope that hit the right points in a relatively succinct way, and 
>> helps.
>> Joel Patterson
>> Database Administrator
>> 904 727-2546

Pole nakazi

Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 
dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku, 
VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, 
o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych, 
NIP: 586-000-78-20, REGON: 190024711--

Other related posts: