2

I have changed the order of columns in my base table, but after that materialized of this table in showing as invalid, even after refreshing it.

Do I need to recreate the materialized view or is there something else I can implement.

Is it happening because I had dropped and recreated the underlying base table.

Please help. Thanks!

Prakash Rai
  • 33
  • 1
  • 5
  • How did you 'change the order' (and why)? By dropping and recreating the table? Did it (and does it) having a related materialised view log table? What error does the MV have - you can see that in the `user_errors` or `all_errors` views. – Alex Poole Jul 28 '14 at 08:13
  • Yes Alex, I had dropped and recreated the table. No it didnt had a related materialized view log table. Actually I didn't get any errors the problem is it doesn't load latest values from base table and there is a small cross icon over the materialized even after a refresh. – Prakash Rai Jul 28 '14 at 09:18
  • A manual refresh doesn't give you any errors? There is nothing for that view in `user_errors`? Does the client that is showing you the red cross give you an option to refresh it or view more information about it and its errors? – Alex Poole Jul 28 '14 at 09:58
  • Yes there is no error while doing a manual refresh as well as in user_errors or all_errrors. Yeah, I am using SQL Developer it has an option to refresh it but no error checking option. – Prakash Rai Jul 28 '14 at 10:24
  • select * from user_objects where status = 'INVALID'; I can see this materialized view in this list – Prakash Rai Jul 28 '14 at 10:36

1 Answers1

3

You do not need to rebuild it. However, if you change an object that the materialized view depends on you will need to recompile the view to validate and compute the staleness of it:

ALTER MATERIALIZED VIEW mview_name COMPILE;

You can check whether your view needs to be recompiled by checking the STALENESS column in USER_MVIEWS for your MV in question. If the column shows "NEEDS_COMPILE", you need to execute above statement.

Have also a look at Invalidating Materialized Views in the Database Data Warehousing Guide and the ALL_MVIEWS Reference in the documentation.

gvenzl
  • 1,861
  • 14
  • 22