Home » SQL & PL/SQL » SQL & PL/SQL » materialized view refresh causing errors in other queries running simaultaneously
materialized view refresh causing errors in other queries running simaultaneously [message #185756] Thu, 03 August 2006 07:44 Go to next message
stephenpir
Messages: 2
Registered: August 2006
Junior Member
Hi,

I'm selecting from a standard view (view1) which references a materialized view (mview1). The query against view1 takes quite a while to run and whilst it's running, the refresh group for mview1 is refreshed by another session.

When this happens the query against view1 errors out with

Message: ORA-08103: object no longer exists

Should this be happening? I thought that using a refresh group to refresh the Mview would prevent this error from occuring.

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

I'm using oracle 8.1.7i by the way.

Cheers
Re: materialized view refresh causing errors in other queries running simaultaneously [message #189574 is a reply to message #185756] Fri, 25 August 2006 02:51 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Maybe, your problem is reported in note 170185.1 (below).
Is it a complete refresh on a single MV?
- Then it uses truncate and insert append, I believe.

Br
Kim Anthonisen



Subject: ORA-08103 Occurs When Performing SELECT Statement
Doc ID: Note:170185.1 Type: PROBLEM
Last Revision Date: 27-APR-2004 Status: PUBLISHED



fact: Oracle Server - Enterprise Edition 8
symptom: Error performing a SELECT statement
symptom: ORA-08103: object no longer exists
symptom: Table is being truncated by other session

symptom: Analyze table validate structure cascade returns no errors
cause: This ORA-08103 occurs on the next block read after the truncate
command.
The LOCK TABLE IN EXCLUSIVE MODE does not prevent the table from being
SELECTED from. Thus, when the query has started and while this query runs
and the truncate occurs, this ORA-08103 may surface on the next block read.
This is considered intended behavior.
When a TRUNCATE occurs the DATAOBJ# in OBJ$ gets increased by one and thus
may lead to this ORA-08103 'object no longer exists'



fix:

Possible solutions are:
- Use DELETE instead of TRUNCATE
- Use SELECT FOR UPDATE as this will try to lock the table

Re: materialized view refresh causing errors in other queries running simaultaneously [message #189598] Fri, 25 August 2006 03:38 Go to previous message
stephenpir
Messages: 2
Registered: August 2006
Junior Member
Hi,

Thanks for the advice. It was because of the mview being refreshed using truncate. Basically when there is only one member of a refresh group and dbms_refresh.refresh is used truncate is used eradicating read consistency. To solve I just added a dummy mview to the refresh group.

Cheers anyway.
Previous Topic: updateable and read-only database views
Next Topic: Table pratitioning
Goto Forum:
  


Current Time: Sun Dec 11 04:06:25 CST 2016

Total time taken to generate the page: 0.07384 seconds