Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!newshub.sdsu.edu!elnk-nf2-pas!newsfeed.earthlink.net!sjc70.webusenet.com!news.webusenet.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: cschang <cschang@maxinter.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Errors from create Materialized View
Date: Fri, 08 Aug 2003 22:33:23 -0400
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <3F345D73.6525275C@maxinter.net>
X-Mailer: Mozilla 4.77 [en] (Win98; U)
X-Accept-Language: zh-TW,en,zh
MIME-Version: 1.0
References: <3F331C5B.FB18FCC5@maxinter.net> <130ba93a.0308080841.484c68fb@posting.google.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: abuse@supernews.com
Lines: 102
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130394


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@maxinter.net> wrote in message news:<3F331C5B.FB18FCC5@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