This week a co-worker and I have been doing some rapid-fire testing to improve fast-refresh performance on an old materialized view, which does some summary aggregations on a 1.9 billion row (and growing) master table. One of the things we tested was using the new-in-11gR2 COMMIT SCN feature. There is a great blog post describing the benefits of this feature by Alberto Dell'Era. To quickly summarize and over-simplify, it provides a much faster way to update rows in the materialized view log that are eligible for refresh and purging. This definitely sounds like something we'd want, so let's roll!

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;

 TABLEOBJ# NAME
---------- ------------------------------
    605668 BAR
    605668 BAR

Unfortunately, that doc also said there was no workaround other than to apply a one-off patch (otherwise fixed in 11.2.0.4 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.

update snap_logdep$
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 o.name='BAR'
)
and rscn is null;


commit;