"Sometimes" error during refresh of MV (ORA-12008 and ORA-01031)
Hi,
I'm not quite sure, where to place this question, but "SQL and PL/SQL" sounds fine.
We run a datawarehouse and "sometimes" get a combination of ORA-12008 and ORA-01031 during the refresh of one of our MV's. The problem is, we can't really find the problem, because normally everything works fine. When this error occurs during our nightly refresh, we can "solve" the problem by trying a refresh a second time. Both refreshs are done using our job scheduling system (UC4) using the same job and login credentials: First refresh fails, second (only a few minutes later) works fine.
And to make it a bit more interesting: We experience this problem only sometimes and for changing materialized views ... During the last the last week we had two such "runtime" problems. We are certain, that no changes on the server, the database and our warehouse implemented, as we are currently in a "frozen zone" with no changes to the production environment allowed. There are no messages written to the alert log, however a trace file like the following is written to disk.
Thanks for any ideas
Michael
---
Trace file /opt/orpadp01/diag/rdbms/adpp/ADPP/trace/ADPP_ora_10098.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /opt/orpadp01/product/11.2.0
System name: SunOS
Node name: nspadb02
Release: 5.10
Version: Generic_144488-17
Machine: sun4u
Instance name: ADPP
Redo thread mounted by this instance: 1
Oracle process number: 83
Unix process pid: 10098, image: oracle@nspadb02
*** 2013-06-06 01:27:20.167
*** SESSION ID:(206.47151) 2013-06-06 01:27:20.172
*** CLIENT ID:() 2013-06-06 01:27:20.172
*** SERVICE NAME:(SYS$USERS) 2013-06-06 01:27:20.172
*** MODULE NAME:(OWB_CCS) 2013-06-06 01:27:20.172
*** ACTION NAME:(EXECUTION) 2013-06-06 01:27:20.172
Failed REFRESH STATEMENT
+/* MV_REFRESH (DEL) */ DELETE FROM "APODP_ONLINE"."MV_DE_KIO_DEPOTUMSATZ_ALL" SNA$ WHERE "KIO_RID" IN (SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "DATVER_DE"."MLOG$_TB_DE_KIO_DEPOTUMSAT" "MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)+
---