Re: Errors from create Materialized View

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

Jusung Yang wrote:

> I think all your problems with the MV stem from the fact that you are
> not familiar with the "on prebuilt table" clause... It means you want
> to create an MV on top of an "existing" table. You can find examples
> here...
>
> http://www.jusungyang.com/DWfolder/MaterializedViews.html
>
> Since the table already exists, you don't need to specify the storage
> clause. "Create materialized view" privilege needs to be granted.
> Also,
> START WITH sysdate NEXT TRUNC(sysdate)+2/24
> will not work, unless you create the MV between midnight and 2 AM.
> 'NEXT' needs to be in the future, of course.
>
> - Jusung Yang
>
> cschang <cschang_at_maxinter.net> wrote in message news:<3F331C5B.FB18FCC5_at_maxinter.net>...
> > My system 8.1.7 on NT 4 sp6a. I tried to create a MV as syntax below,
> > while compiled it, I faced few problems which I could not figure out.
> > And by searching on web, most of the documents, including the Oracle,
> > did not give me clear clues.
> > I have created all the MATERIALIZED VIEW LOGs on the tables involved
> > with their primary keys.
> > I also set up QUERY_REWRITE_INTEEGRITY on the system.
> >
> > CREATE MATERIALIZED VIEW mv_consumption
> > NOLOGGING NOCACHE
> > TABLESPACE ipvsys_dbview
> > ON PREBUILT TABLE
> > REFRESH complete
> > START WITH sysdate NEXT TRUNC(sysdate)+2/24 -- rerun at 2 am EST
> > ENABLE QUERY REWRITE
> > AS
> > SELECT c.vendor_id, c.vendor_list, c.vendor_lin,
> > c.vendor_price, c.qty_period, c.contract_id, c.vendor_qty,
> > c.vendor_um,
> > DECODE(s.reference_date,NULL,
> > TO_CHAR(s.source_file_date,'MM/DD/YYYY'),TO_CHAR(s.reference_date,'MM/DD/YYYY')),
> >
> > r.qty, r.qty_unit
> > FROM catalog_items c, vp$source_lists s, receipts r, po_lines p,
> > po_heads h
> > WHERE s.list = c.vendor_list
> > AND r.niin = c.niin
> > AND FLAG = 'N'
> > AND purchase_type <>'SPOTBUY'
> > AND order_type <> 'SPOTBUY'
> > AND price_type = 'S'
> > AND r.po_id = p.po_id
> > AND r.req_num = p.req_num
> > AND r.niin = p.niin
> > AND p.po_id = h.po_id;
> >
> > Errors
> > 1. With "ON PREBUILT TABLE" (follow the formula of Oracle), I got the
> > "Missing Option" error.
> > 2. Taken out the "ON PREBUILT TABLE", by combining REFRESH COMPLETE ON
> > DEMAND, I still got the same error ( which the Oracle manual did not say
> > I cannot)
> > 3. Remove the "ON DEMAND", I got the "Insufficient Privileges" error
> > to the last statement of the query " AND p.po_id = h.po_id". Because
> > logged in as the Schema owner, who actually create all the tables
> > involved.
> > I also tried to include the a "STORAGE ( )" part and it gave me the
> > same error of Missing Option.
> >
> > So My questions
> > 1. What exact orders of these option ?
> > 2. Which one can combine and which exclude each other?
> > 3. Not too much documents of what "ON PREBUILT TABLE" exactly does?
> > 4. What exact the Insufficient Privileges mean here?
> > 5. Because I am also setting up environment for Function-based index,
> > so I set the QUERY_REWRITE_INTEGRITY=TRUSTED. Does it affect the MV
> > performance? some document mention it has to set =stale_.., So which one
> > should be.
> >
> > C Chang
Received on Fri Aug 08 2003 - 21:33:23 CDT