Home » SQL & PL/SQL » SQL & PL/SQL » Unable to create materialized view over db link
Unable to create materialized view over db link [message #207858] Thu, 07 December 2006 03:55 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 #207864 is a reply to message #207858] Thu, 07 December 2006 03:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
some other post in ORAFAQ only.
Check
By
Vamsi
Re: Unable to create materialized view over db link [message #207895 is a reply to message #207858] Thu, 07 December 2006 04:47 Go to previous messageGo to next message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

Thanks Vamsi, But if u hv read my messg then you would knw tht I am looking for a FAST REFRESH of MV over the DB LINK.
Re: Unable to create materialized view over db link [message #207911 is a reply to message #207895] Thu, 07 December 2006 05:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
please replace your keyboard!
Re: Unable to create materialized view over db link [message #208030 is a reply to message #207911] Thu, 07 December 2006 20:32 Go to previous message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

Frank: Thanks but no thanks for this advice.
Previous Topic: Materialized view refresh (merged 2 threads)
Next Topic: create index on a table with VARRAY data type
Goto Forum:
  


Current Time: Mon Dec 05 08:43:54 CST 2016

Total time taken to generate the page: 0.08044 seconds