dbms_mview.explain_mview to figure out why fast refresh failed with ORA-32314

Q: Fast refresh failed with message "ORA-32314: REFRESH FAST of "U1"."MV_T1" unsupported after deletes/updates"
A: DBMS_MVIEW.EXPLAIN_MVIEW is for this purpose.

Case
SQL> conn u1/***
Connected.
SQL> @?/rdbms/admin/utlxmv.sql

Table created.

SQL> EXEC dbms_mview.explain_mview(‘"U1"."MV_T1"’);

PL/SQL procedure successfully completed.

SQL> desc MV_CAPABILITIES_TABLE
 Name                                      Null?    Type
 —————————————– ——– —————————-
 STATEMENT_ID                                       VARCHAR2(30)
 MVOWNER                                            VARCHAR2(30)
 MVNAME                                             VARCHAR2(30)
 CAPABILITY_NAME                                    VARCHAR2(30)
 POSSIBLE                                           CHAR(1)
 RELATED_TEXT                                       VARCHAR2(2000)
 RELATED_NUM                                        NUMBER
 MSGNO                                              NUMBER(38)
 MSGTXT                                             VARCHAR2(2000)
 SEQ                                                NUMBER

SQL> select capability_name,possible,msgtxt from mv_capabilities_table where capability_name like %FAST%’ order by seq;

CAPABILITY_NAME                P MSGTXT
—————————— – —————————————————————————————————-
REFRESH_FAST                    Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML     N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N PCT is not possible on any of the detail tables in the materialized view

Solution
rewrite MV based on DBMS_MVIEW.EXPLAIN_MVIEW output.

Things behind
You get to know what MV can do and can’t from "Oracle® Database Data Warehousing Guide".
Chapter "8 Basic Materialized Views"
.General Restrictions on Fast Refresh
.Restrictions on Fast Refresh on Materialized Views with Joins Only
.Restrictions on Fast Refresh on Materialized Views with Aggregates
.Restrictions on Fast Refresh on Materialized Views with UNION ALL

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Oracle database. Bookmark the permalink.

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