RE: mview over private database link
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <remigiusz.sokolowski@xxxxxxxxxx>
- Date: Fri, 7 Oct 2011 08:45:02 -0400
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@xxxxxxxxxx]
Sent: Friday, October 07, 2011 8:30 AM
To: Patterson, Joel
Cc: oracle-l@xxxxxxxxxxxxx
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@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:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
