DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to set automatic referesh for materialized view?

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    how to set automatic referesh for materialized view?

    hi,

    the version of my oracle is 9i.

    i have created materialzed view using the following query.

    CREATE MATERIALIZED VIEW BALE_STOCK
    AS select bale_no,invoice_no,mtrs,rec_dt,batch,mcode,
    loc,value from bale_inward where bale_no in(select bale_no from
    bale_inward minus select distinct bale_no from bale_outward
    union
    select distinct bale_no from bale_gr
    minus
    select bale_no from(
    select bale_no,count(*) from bale_outward group by bale_no
    having count(*) > 1
    and bale_no in(select bale_no from(select bale_no,count(*) from
    bale_gr group by bale_no having count(*) >= 1))))

    snapshot is created.

    this is not allowing the the options either "fast autofresh on commit"
    or "auto refresh on commit".

    plz suggest the way to refresh or update this view table automatically
    on commiting the source tables of this view.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Did you try...
    Code:
    CREATE MATERIALIZED VIEW BALE_STOCK
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS
    select bale_no,...
    Do you have the right privs on base tables?
    What's the error message you are getting?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2006
    Posts
    377

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width