Materialized Views

[Peer Jones]

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

[TomazZ]

It should work with "refresh on commit". But it doesn't work in every situation (like across dblinks). Read in manuals about restrictions.

[Peer Jones]

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

[slimdave]

can you post the create mv statement?

[Peer Jones]

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

[balajiyes]

if u r planning to select all the columns from the table... You can aswell select from the TABLE...

[Peer Jones]

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

[balajiyes]

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!