| Unable to create materialized view over db link [message #207858] |
Thu, 07 December 2006 03:55  |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
