Materialized Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Materialized Views

  1. #1
    Join Date
    Nov 2001
    Posts
    118

    Materialized Views

    Hi Everyone,

    I know this seems silly, but I'm doing something wrong with materialized views, and somehow I can't seem to figure it out, however much I go into the manuals:

    I have a simple materialized view, and all I want it to do is refresh its values automatically whenever the underlying tables (or views, or whatever) are altered, so it reflects the changed data.

    Somehow I can't get the refresh to work and I can only see the old data state.

    I've tried refresh force, refresh fast, materialized view logs, and other stuff, but I must be missing something.

    Can anyone help me please?

    Thanks
    Peer

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    It should work with "refresh on commit". But it doesn't work in every situation (like across dblinks). Read in manuals about restrictions.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  3. #3
    Join Date
    Nov 2001
    Posts
    118
    Thank you, for some reason I must have done something wrong, because it works now.

    But do you know why the same MV with refresh on commit doesn't work on a view based 1:1 on that table? (Message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view)

    I'm working my way step-by-step towards solving an issue of which this seems to be one of several problems.

    Bye
    Peer

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    can you post the create mv statement?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2001
    Posts
    118
    Yes, sure; Here is the whole simplified thing leading to the above message:

    create table a
    (one number
    ,two number
    ,constraint a_pk primary key (one)
    )
    /

    create view a_v as select one,two from a
    /

    create materialized view a_mv
    refresh on commit as select one,two from a_v
    /

    This leads to: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view (table and view are successfully created).

    The statement:

    create materialized view a_mv
    refresh on commit as select one,two from a
    /

    works OK.

    Bye
    Peer

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    if u r planning to select all the columns from the table... You can aswell select from the TABLE...
    Cheers!
    OraKid.

  7. #7
    Join Date
    Nov 2001
    Posts
    118
    It's not that. I'm just trying to get to the bottom of a larger problem by breaking the whole thing down into small managable components.

    And the above problem is one of the small bits that don't seem to act as they should, in spite of their simplicity.

    Bye
    Peer

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi,
    I think yu can't give REFRESH ON COMMIT on a view. since the data for the view is retrieved from a table. Only the table gets commited and not the view.
    Iam not sure whether my explanation is correct. I can Just think of this.

    Cheers!

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