Friday, December 6, 2013

R12: Materialized Views

You will probably find this best as a "oh yeah" kind of reminder, but I learned a lesson during our R12 upgrade when we went live: treat MVs just like you treat your ETLs and not only test them prior, but validate after as well.

Obviously we did test, but sometimes in a huge project not all changes get propagated as they should so we have MVs without column modifications (especially for RA_CUSTOMERS).  Additionally, we ran into this in our alert log:

ORA-12012: error on auto execute of job 93
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
Why did we encounter this?  We didn't validate that the MV was in a VALID state before asking the system to run it as per the normal run schedule.  This leads me to think of yet another alert to tell us when we have a MV go INVALID just in case it happens again and we aren't watching the alert log like hawks.

No comments:

Post a Comment