RE: mview over private database link
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <remigiusz.sokolowski@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Oct 2011 14:26:27 -0400
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
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Remigiusz Sokolowski
Sent: Thursday, October 06, 2011 5:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: mview over private database link
Now all is clear to me:
Testcase pasted below
here excerpt from previous posting:
"let's assume we have a schema MIDDLE within the DEST database on which
we defined a private remote link to remote database SRC.
I am able to create in the MIDDLE schema materialized views and views
based on tables from the SRC db.
I have also another schema TARGET, where I am able to create
materialized view on materialized views from MIDDLE schema.
I am not able however to create mviews based on views from the MIDDLE
schema, even though I am able to select those views as the TARGET user.
So it seems the TARGET user can select on rights of the MIDDLE user, but
can not select for building a mview.
and
user SRC
----------------
create user src identified by src quota unlimited on users;
grant connect to src;
grant create table to src;
create table src.test(a number, b varchar2(20)) tablespace users;
insert into src.test values(1,'test');
commit;
user MIDDLE
---------------
create user middle identified by middle default tablespace users quota
unlimited on users;
grant connect to middle;
grant create database link, create view, create materialized view,
create table to middle;
connect middle/middle
CREATE DATABASE LINK DL_SRC_SRC
CONNECT TO SRC IDENTIFIED BY "src"
USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST =
<host>)(PORT = 1521)))(CONNECT_DATA=(SID = <sid>)))';
create view middle.v_test1 as select * from src.test@DL_SRC_SRC;
create view middle.v_test2 as select * from test@DL_SRC_SRC;
create materialized view middle.mv_test1 tablespace users as select *
from middle.v_test1;
create materialized view middle.mv_test2 tablespace users as select *
from middle.v_test2;
user TARGET - last 2 command end with errors - anyway the point is that
it seems views from MIDDLE are performed on TARGET connections thus
failing - even though direct call to those views is performed properly
on connection by MIDDLE
------------------
create user target identified by target default tablespace users quota
unlimited on users;
grant connect to target;
grant create materialized view, create table to target;
grant select on middle.v_test1 to target;
grant select on middle.v_test2 to target;
connect target/target
create materialized view target.mv_test1 tablespace users as select *
from middle.v_test1;
create materialized view target.mv_test2 tablespace users as select *
from middle.v_test2;
TIA, Remigiusz
--
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
