RE: mview over private database link

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 7 Oct 2011 08:45:02 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA17B4F3D624_at_JAXMSG01.crowley.com>



I'm not suggesting here one over the other as I am not sure of the entire scope.

To address point 2... I do not see why the same grants and privileges could not be the same for the view and MV. (not withstanding the ability to refresh privilege).

As to the database link. A database link is necessary for the MV also to work.... so same issues as to privs.

If I understand your architecture better; SRC is a schema on a SOURCE database. MIDDLE is a schema on another database -- call it database B. Before going further, Middle can hold the MV or a view instead.

As I have been reviewing your description then TARGET schema(s) are not in a third database, but also reside in database B.

If this scenario is true, then MIDDLE can own the DB link to a schema on SRC with limited privs, (read only on target tables). This will work for both MV and view. Target schemas can have read on middle.mv or middle.view.

I'll stop here since I am not sure if I understood your architecture exactly. So as to which is better, it would have to be based on performance -- to which I say, a MV based on table would allow changes to come over hourly or however.

If the original MV (owned by middle that links to Src) is based on a view in Src, then this is another story I suppose -- where I probably would suggest not basing it on the view. Perhaps others here have input.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: Remigiusz Sokolowski [mailto:remigiusz.sokolowski_at_nordea.com] Sent: Friday, October 07, 2011 8:30 AM
To: Patterson, Joel
Cc: oracle-l_at_freelists.org
Subject: Re: mview over private database link

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-l
Received on Fri Oct 07 2011 - 07:45:02 CDT

Original text of this message