Re: mview over private database link
Date: Fri, 7 Oct 2011 14:29:42 +0200
Message-ID: <4E8EF0B6.5000308_at_nordea.com>
hi,
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_at_crowley.com 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_at_nordea.com]
> Sent: Friday, October 07, 2011 2:02 AM
> To: Patterson, Joel
> Cc: oracle-l_at_freelists.org
> 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_at_crowley.com 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_at_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_at_nordea.com> 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: 0000021828, 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-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 07 2011 - 07:29:42 CDT