Oracle Replication Fast Refresh a nested MV

From: Ivo <ivomaya_at_hotmail.com>
Date: 19 Aug 2002 20:03:51 -0700
Message-ID: <abfe1e55.0208191903.474ddad1_at_posting.google.com>

Now. I have two databases. One is production (7.3.4.4) and the other one is reporting (8.1.7.4.1) database. I need to replicate some 20 tables from production (master ) DB to the replica DB. I do that as:  

I have 3 tables (AA, BB, and CC) on database 001 (parent or master). I have a database 002 (replica).

1. I create snapshot logs on these 3 tables on DB 001. 
2. I create a database link on DB 002 to link to DB 001.
3. I create snapshots on DB 002 using the DB link to DB 001 for tables

AA, BB,
and CC.
4. Now I have 3 replicated tables on DB 002 ( AA1, BB1, and CC1) 5. I create 3 MV logs on AA1, BB1, and CC1. So far everything works fine. All tables have PKs.
6. Now I want to create a nested (or snapshot with joins only, but I have to trick somehow Oracle to think those are only tables, but not snapshots) snapshot based on (AA1, BB1, and CC1) as: CREATE MATERIALIZED VIEW TEST
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT

AA1.ROWID alias_01,
AA2.ROWID alias_02,
AA3.ROWID alias_03,
AA1.COLUMN_01 alias_AAA,
AA1.COLUMN_02 alias_BBB,
AA2.COLUMN_01 alias_CCC,
AA2.COLUMN_02 alias_DDD,
AA3.COLUMN_01 alias_EEE,
AA3.COLUMN_02 alias_FFF

FROM
AA1,
AA2,
AA3
WHERE
AA1.PK_COLUMN = AA2.EQUVALENT (+) AND
AA3.PK_COLUMN = AA2.EQUVALENT (+)
/

7. Here I got an error:
ERROR at line 16:
ORA-12053: this is not a valid nested materialized view 8. It points to the first reference from the FROM clause. 9. When I change the REFRESH mode from FAST to FORCE it works, but 10. When I try exec
dbms_mview.refresh('mv_case','f','',false,true,1,1,2,false); I get another error:
ORA-30450: refresh_after_errors was TRUE; The following MVs could not be
refreshed: TEST
That is all.

There must be a workaround this problem as this thing use to work just fine.

Any help - greatly appreciated

Thanks Received on Tue Aug 20 2002 - 05:03:51 CEST