|Unable to create materialized view over db link [message #207857]
||Thu, 07 December 2006 03:53
Registered: March 2006
I've got two databases say DB1, DB2.
I have got 2 tables in DB1 say "SCOTT.EMP", "SCOTT.DEPT"
I've got a readonly user on DB1 say "ROSCOTT" which has got SELECT ANY TABLE, SELECT ANY DICTIONARY, CREATE SESSION privileges.
In DB2 there is a user say "BROCK" with roles CONNECT and RESOURCE.
My requirement is to create a Materialized View say "MV_DB2" on DB2 for the SCOTT.EMP, SCOTT.DEPT tables. "MV_DB2" should be preferrably FAST REFRESH as the Materialized Logs for EMP and DEPT are already created.
For which I've done the following:
As a SCOTT user, Materialized logs on SCOTT.EMP, SCOTT.DEPT tables were created WITH ROWID option. Select on EMP, DEPT tables and their MV logs are granted to "ROSCOTT".
From DB2: CONN BROCK/BROCK
CREATE PUBLIC DATABASE LINK ROSCOTT_DB1_LINK
CONNECT AS ROSCOTT IDENTIFIED BY ROSCOTT
External database link created and test on the above dblink was successful.
CREATE MATERIALIZED VIEW MV_DB2
START WITH SYSDATE
ENABLE QUERY REWRITE
SELECT X.ENAME, Y.DNAME,
X.ROWID emprowid, Y.ROWID deptrowid
Initially I got insufficient privileges error, for which I've granted GLOBAL QUERY REWRITE privilege to "BROCK" of DB2. Now, I'm having an ORA-12015 error. It tells that this is a complex materialized view. Is it not possible to have a fast refresh with a joined base tables from a remote db??
DB Version: Oracle 220.127.116.11