Re: Errors from create Materialized View

Well, not exactly sure. I have not used 8i for a couple of years now :-)
It is possible that privileges contained within certain 'roles' have changed between 8i and 9i. I don't seem to have problem creating MV with just 'connect and resource' role and 'create MV' privilege. My guess is 'query rewrite' is giving the errors. Try 'grant global query rewrite' to the user.

I would suggest that you start with the simplest MV. "create materialized view mv_name as select * from table_name" would do. And gradually add more stuff to it. You probably won't have to worry about the query rewrite stuff just yet. The MV log has nothing to do with the errors you got while creating the MV.

• Jusung Yang

cschang <cschang_at_maxinter.net> wrote in message news:<3F345D73.6525275C_at_maxinter.net>...
> JuSung:
> Thank you for your help. This is my first MV that I tried to create. By reading your
> examples, now I understand what ?ON PREBUILT TABLE? means. I decided to take it out and
> also use the BUILD IMMEDIATE REFRESH FAST and NEXT ?. However I am still getting the error
> of ?Insufficient Privileges? for the same last statement of my syntax even I used the sys
> user to grant the ?create materialized view on my_user?, which is actually the ?CREATE
> SNAPPSHOT? privilege. I even tried with the ?CREATE ALL SNAPSHOT?. Is my 8.1.7 database
> missing something? Or is because I did not create the materialized view log right on that
> particular table. Here was the syntax I used to create the log
>
> CREATE MATERIALIZED VIEW LOG ON po_heads
> NOLOGGING
> TABLESPACE ipvsys_mv_log
> WITH PRIMARY KEY
> INCLUDING NEW VALUES;
>
> Appreciate your help. I will visit more to your web site.
>
> C Chang
>
>
Received on Sat Aug 09 2003 - 01:14:28 CDT