Materialized View with DBLink: ORA-00600: internal error code, arguments: [kkzuasid]

Hello guys!
Not being able to refresh you Materialized View because of this error?

bamdb> exec dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C');
BEGIN dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C'); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzuasid], [2], [0], [1], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

The bad new is there is no workaround (I usually prefer workaround for this, is quicker and less complicated).
But the good new is there is a patch for this: Patch 17705023 : ORA-600 [KKZUASID] ON MV REFRESH

This error is related to a defect when trying to refresh a materialized view and using Query Rewrite in RDBMS 11.2.0.4, and is fixed in 12.2 (Bug 17705023 : ORA-600 [KKZUASID] ON MV REFRESH).
You can find more info in MOS Bug 17705023 – ORA-600 [kkzuasid] on MV refresh (Doc ID 17705023.8).

In my situation, as per documentation, I applied the patch and solved the situation as quick as possible. But reviewing the situation to write this post, specially about Query Rewrite feature, I see you maybe can recreate you materialized view with hint NOREWRITE OR setting parameter QUERY_REWRITE_ENABLED to false and have a shot. Maybe an undocumented Workaround? 🙂

If you make this, please add your experience as a comment!

After applying patch, of course:

bamdb> exec dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C');

PL/SQL procedure successfully completed.

Bye bye, see you next Wednesday!

Matheus.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s