Re: Help rewriting query

On 17 Nov 2005 06:43:15 -0800, "News" <Contact_404_at_hotmail.com> wrote:

>News a écrit :
>
>> Now try please in the same sample inserting row and fast refreshing!
>> The MV is updated only when I issue a complete refresh. It works fine
>> in 9i. I tried it with refresh on commit, refresh on demand and when
>> t1, t2 and t3 are different.. Is there in 10g any parameter I must set
>> for the MV to be updated?
>
>Fast refresh didn't happen because I created MV in sys schemas. I
>figured it out cause I didn't see created objects with dbArtisan.
>When I created logs and MV in SCOTT schemas it worked fine !!
>
>It's not important cause it was just test but if someone could explain
>this ..

 Have a look in the SQL Reference manual, the page for CREATE MATERIALIZED VIEW LOG. About halfway down, under "Semantics", there's:

"You cannot create a materialized view log for a table in the schema of the user SYS."

 You shouldn't be creating things in the SYS schema anyway, and in this case there's a further reason that it just won't work.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool