A recent discussion (https://community.oracle.com/message/13240100) triggered me to check he DBMS_MVIEW.EXPLAIN_MVIEW logic.
This procedure gives information about a materialized view. Especially reasons why query rewrite or fast refreshes are not possible.
The simple version needs a table MV_CAPABILITIES_TABLE. In most systems this table is not installed. But the second version of the EXPLAIN_MVIEW logic can be used. It returns a varray of ExplainMVMessage type.
Here is a simple pipelined function that allows to call this dbms_package.
create or replace
function explainMview (p_mview in varchar2) return SYS.ExplainMVArrayType pipelined
authid current_user
as
pragma autonomous_transaction;
v_expl SYS.ExplainMVArrayType;
v_msg SYS.ExplainMVMessage;
v_ind binary_integer;
begin
dbms_mview.explain_mview(mv => p_mview, msg_array => v_expl);
commit;
v_ind := v_expl.first;
loop
v_msg := v_expl(v_ind);
pipe row (v_msg);
exit when v_ind >= v_expl.last;
v_ind := v_expl.next(v_ind);
end loop;
end explainMview;
In general I install that package in some DBA account. And then give
grant execute on explainMview to someSchema;
After that any relevant users can use it to get the explaination.
here is an example:
select * from table(isdba.explainMview('MYSCHEMA.MV_CLI_PROGRAMME'));
| MVOWNER | MVNAME | CAPABILITY_NAME | POSSIBLE | RELATED_TEXT | RELATED_NUM | MSGNO | MSGTXT | SEQ |
|---|---|---|---|---|---|---|---|---|
| MYSCHEMA | MV_CLI_PROGRAMME_USER | PCT | F | 0 | 0 | 1 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_COMPLETE | T | 0 | 0 | 1002 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_FAST | T | 0 | 0 | 2003 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE | F | 0 | 0 | 3004 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | PCT_TABLE | F | ESM.PERSON | 216 | 2068 | relation is not a partitioned table | 4005 |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | PCT_TABLE | F | ESM.PERSONTEAMPERSON | 262 | 2068 | relation is not a partitioned table | 4006 |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_FAST_AFTER_INSERT | T | 0 | 0 | 5007 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_FAST_AFTER_ONETAB_DML | T | 0 | 0 | 6008 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_FAST_AFTER_ANY_DML | T | 0 | 0 | 7009 | ||
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REFRESH_FAST_PCT | F | 0 | 2197 | PCT FAST REFRESH is not possible if query contains a remote table | 8010 | |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_FULL_TEXT_MATCH | F | ESM.PERSON | 216 | 2099 | mv references a remote table or view in the FROM list | 9011 |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_FULL_TEXT_MATCH | F | ESM.PERSONTEAMPERSON | 262 | 2099 | mv references a remote table or view in the FROM list | 9012 |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_FULL_TEXT_MATCH | F | 0 | 2159 | query rewrite is disabled on the materialized view | 9013 | |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_PARTIAL_TEXT_MATCH | F | 0 | 2159 | query rewrite is disabled on the materialized view | 10014 | |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_GENERAL | F | 0 | 2159 | query rewrite is disabled on the materialized view | 11015 | |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | REWRITE_PCT | F | 0 | 2158 | general rewrite is not possible or PCT is not possible on any of the detail tables | 12016 | |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | PCT_TABLE_REWRITE | F | ESM.PERSON | 216 | 2068 | relation is not a partitioned table | 13017 |
| MYSCHEMA | MV_CLI_PROGRAMME_USER | PCT_TABLE_REWRITE | F | ESM.PERSONTEAMPERSON | 262 | 2068 | relation is not a partitioned table | 13018 |
svenweller 