Materialized views with fast refresh
- From: "Niki Tragen" <n.tragen@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 Apr 2006 14:51:07 +0100
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@linktest a,
qlsdba.stcsessd@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 "
--
//www.freelists.org/webpage/oracle-l
Other related posts:
