Home » Server Options » Replication » Unable to create materialized view over db link
Unable to create materialized view over db link [message #207857] Thu, 07 December 2006 03:53 Go to next message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

Hi All,

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:
From DB1:
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
USING '<tns_entry_of_DB1>';
External database link created and test on the above dblink was successful.

CREATE MATERIALIZED VIEW MV_DB2
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+1/24
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT X.ENAME, Y.DNAME,
X.ROWID emprowid, Y.ROWID deptrowid
FROM
SCOTT.EMP@ROSCOTT_DB1_LINK X,
SCOTT.DEPT@ROSCOTT_DB1_LINK Y
WHERE
X.JOB='CLERK' AND
Y.LOC='GENEVA' AND
X.DEPTNO=Y.DEPTNO;

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 9.2.0.6

Re: Unable to create materialized view over db link [message #207874 is a reply to message #207857] Thu, 07 December 2006 04:14 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, don't cross-post
Previous Topic: multimaster
Next Topic: links are not properly working!
Goto Forum:
  


Current Time: Thu Mar 28 15:33:22 CDT 2024