Materialized views with fast refresh

From: Niki Tragen <n.tragen_at_mmu.ac.uk>
Date: Fri, 21 Apr 2006 14:51:07 +0100
Message-Id: <4448F15B0200007500009FFE@gonner.all.adm.mmu.ac.uk>

Hi

I've been trying to run materialized views from one database to another, ie the master tables are not in the same instance as the mat views. I'm using a database link to reference the tables in the remote database. If I use a materialized view referencing only one table the view is created ok but as soon as I put a join in the view it fails with the error

ORA-12015 -cannot create a fast refresh materialized view from a complex query.

I've googled on this and made changes to the log creation so to include rowid and used these in the select with the join but still get the same error.

If I create the materialized view (using same code with joins) within the same database as the logs it works ok.

Is this a limitation of materialized views or is there something fundamental that I'm missing.

I've included the code below.

Thanks for any help

Niki

CREATE MATERIALIZED VIEW LOG ON qlsdba.stcstatd WITH SEQUENCE,ROWID
INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON qlsdba.stcsessd WITH SEQUENCE, ROWID
INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW busfeed.stcstatd_stcsessd_mv REFRESH FAST ON DEMAND
AS
SELECT

        a.rowid AS stat_rowid


,       b.rowid AS sess_rowid

,       a.aos_code AS curr_code1,

        b.aos_code AS curr_code


,       b.aos_period AS curr_occurrence

,       b.acad_period AS acad_period

/*,     a.full_desc AS curr_full_desc */
/*,     a.short_desc AS curr_short_desc */
/*,     a.dept_code AS dept_code */
/*,     a.aos_type AS curr_type */
FROM
        qlsdba.stcstatd_at_linktest a,
        qlsdba.stcsessd_at_linktest b
WHERE   
        b.aos_code = a.aos_code /*

 AND b.acad_period IN('05/06','06/07')*/ ;

Niki Tragen
Oracle DBA, System Support Officer
MIDAS
Manchester Metropolitan University
All Saints Building
Oxford Road
M15 6BH

tel: 0044 161 247 1812
fax: 0044 161 247 6829

"Before acting on this email or opening any attachments you should read the Manchester Metropolitan University's email disclaimer available on its website
http://www.mmu.ac.uk/emaildisclaimer "

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 21 2006 - 08:51:07 CDT