Well we quickly hit a snag when testing our creation script the second time around, when it wouldn't let us drop the materialized view log:
SQL> DROP MATERIALIZED VIEW LOG ON FOO.BAR
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzlpllg:5], , , , , ,, , , , , 
We found that we also could no longer perform fast refreshes of the materialized view, getting the same ORA-00600 error. Our initial MOS search turned up Doc ID 14158012.8, which indicates Bug 14158012 (Orphan rows in SNAP_LOGDEP$ causes ORA-600 [kkzlpllg:5]). The bug description is:
With this bug, when there are orphan rows in SNAP_LOGDEP$ with RSCN=NULL,
a CREATE MATERIALIZED VIEW or DROP MATERIALIZED VIEW statement will report ORA-600 [kkzlpllg:5].
I verified that we did have such NULL RSCN values in SNAP_LOGDEP$ related to the master table here.
SQL> select d.tableobj#, o.name
2 from sys.snap_logdep$ d, sys.obj$ o
3 where d.tableobj# = o.obj#
4 and o.name='BAR'
5 and d.rscn is null;
Unfortunately, that doc also said there was no workaround other than to apply a one-off patch (otherwise fixed in 18.104.22.168 and 12c)! Not exactly the quick fix we were hoping for.
However, we did some more searching and found Doc 1310296.1: Internal Error ORA-00600 [kkzlpllg:5] When Executing Drop Materialized View Log
Pretty much the same thing, only this gives us a workaround:
Since the information stored in the Materialized View log is bogus in any case, the Materialized View Log is not useable anymore. The only option is to drop this object and re-create it. To make this possible, a dummy non-NULL value needs to be written into the RSCN column for that table.So we update those rows to set RSCN to a dummy value (9999):
SQL> UPDATE snap_logdep$
2 SET RSCN = 9999
3 WHERE tableobj# = 605668
4 AND RSCN IS NULL;
And we were able to drop the materialized view log and resume testing afterwards.
Hopefully this article saves someone from hitting the same initial roadblock that I did. Especially nice to see the Oracle support docs contradicting themselves!
We also made another interesting find with this particular materialized view that I'll be blogging about later. Definitely an eye-opener, face-palmer and forehead-smacker all in one.
UPDATE - 8 April 2014
Nothing mind blowing, but a little time saver if you know the object name. This doesn't take the owner into account, so be careful out there.
set rscn = 9999
where tableobj# = (
select distinct d.tableobj#
from sys.snap_logdep$ d, sys.obj$ o
where d.tableobj# = o.obj#
and d.rscn is null
and rscn is null;