Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Errors from create Materialized View
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.
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 - 11:41:31 CDT