-1

I'm selecting from a standard view (view1) which references a materialized view (mview1). The standard view is created with "FORCE" option and the materialized view is created "REFRESH COMPLETE ON DEMAND". As I don't know when the materialized view is refreshed (who or what demands the refresh) I cannot avoid the refresh of the materialized view at the same time it is used in the normal view query.

When this happens simultaneously the query against view1 errors out with:

Message: ORA-08103: object no longer exists

I thought that changing the "COMPLETE" option with the "FORCE" one in the materialized view would prevent this error from occurring.

If you can shed some light on this and/or offer a solution it would be greatly appreciated.

Cheers

xavier
  • 177
  • 3
  • 15
  • How do you refresh the MV? – GolezTrol Nov 26 '18 at 11:35
  • As I said in the description of the problem "I don't know when the materialized view is refreshed (who or what demands the refresh)". Many processes are running behind. I do not have DB administrator privileges so I cannot check. I was wondering if a change in the refresh mode of the materialized view would solve the issue. – xavier Nov 26 '18 at 11:38
  • 1
    The problem in any way is related to Oracle doing a truncate, basically kicking away all the data from under the query's feet. There is one possible solution on [OraFaq.com](http://www.orafaq.com/forum/t/67288/). The person asking the question solved it by adding an extra dummy view to the refresh group to prevent that. There may be other ways to this as well. – GolezTrol Nov 26 '18 at 11:39

1 Answers1

1

First do not drop and create the materialized view, refresh it using DBMS_MVIEW.REFRESH procedure - DROP is what leads to the ORA-08103: object no longer exists

You need to set the atomic_refresh parameter of the DBMS_MVIEW.REFRESH to true.

This will refresh the materialize view in a single transaction, so the materialize view is never empty.

Technically a DELETE, INSERT and commit is performed on the materialize view table, so until commited, you see the old state, after commit the new state.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53