Oracle 物化视图 详细错误描述 查看方法

nceiembdew

　　
　　Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。关于物化视图相关的说明参考：
　　
　　Oracle 物化视图
　　http://blog.csdn.net/tianlesoftware/article/details/4713553
　　
　　Oracle 物化视图 快速刷新 限制 说明
　　http://blog.csdn.net/tianlesoftware/article/details/7719679
　　
　　在我们创建物化视图失败时，Oracle给的错误信息过于简单，不能帮助我们定位具体的问题，Oracle 为此提供了dbms_mview.explain_mview过程帮助我们快速定位问题的原因。
　　
一.EXPLAIN_MVIEW说明
　　使用explain_mview过程先要建立mv_capabilities_table表，建表的脚步是$oracle_home/rdbms/admin/utlxmv.sql。（explain_mview过程是两个过程的重载，一个输出到mv_capabilities_table表，另一个以pl/sql的varray格式输出）。
　　
　　SQL>@?/rdbms/admin/utlxmv.sql
　　Table created.
　　
　　查看utlxmv.sql 脚本，可以看到mv_capabilities_tables 表的定义：
　　
　　CREATETABLEMV_CAPABILITIES_TABLE
　　 (STATEMENT_ID VARCHAR(30),--Client-supplied unique statement identifier
　　 MVOWNER VARCHAR(30),-- NULLfor SELECT based EXPLAIN_MVIEW
　　 MVNAME VARCHAR(30),-- NULLfor SELECT based EXPLAIN_MVIEW
　　 CAPABILITY_NAME VARCHAR(30), -- A descriptivename of the particular
　　--capability:
　　--REWRITE
　　-- Can do at least full text match
　　-- rewrite
　　--REWRITE_PARTIAL_TEXT_MATCH
　　 -- Can do at leat full and partial
　　-- text match rewrite
　　--REWRITE_GENERAL
　　-- Can do all forms of rewrite
　　 -- REFRESH
　　-- Can do at least complete refresh
　　--REFRESH_FROM_LOG_AFTER_INSERT
　　-- Can do fast refresh from an mv log
　　-- or change capture table at least
　　-- when update operations are
　　-- restricted to INSERT
　　--REFRESH_FROM_LOG_AFTER_ANY
　　-- can do fast refresh from an mv log
　　-- or change capture table after any
　　 --combination of updates
　　-- PCT
　　-- Can do Enhanced Update Tracking on
　　-- the table named in the RELATED_NAME
　　 --column. EUT is needed for fast
　　-- refresh after partitioned
　　-- maintenance operations on the table
　　-- named in the RELATED_NAME column
　　-- and to do non-stale tolerated
　　-- rewrite when the mv is partially
　　-- stale with respect to the table
　　-- named in the RELATED_NAME column.
　　-- EUT can also sometimes enable fast
　　-- refresh of updates to the table
　　 --named in the RELATED_NAME column
　　-- when fast refresh from an mv log
　　-- or change capture table is not
　　-- possilbe.
　　POSSIBLE CHARACTER(1), -- T = capabilityis possible
　　-- F =capability is not possible
　　 RELATED_TEXT VARCHAR(2000),-- Owner.table.column, alias name,etc.
　　-- related to this message. The
　　--specific meaning of this column
　　--depends on the MSGNO column. See
　　-- thedocumentation for
　　--DBMS_MVIEW.EXPLAIN_MVIEW() for details
　　 RELATED_NUM NUMBER, -- When there is a numeric value
　　--associated with a row, it goes here.
　　 -- The specific meaning of thiscolumn
　　--depends on the MSGNO column. See
　　-- thedocumentation for
　　--DBMS_MVIEW.EXPLAIN_MVIEW() for details
　　 MSGNO INTEGER, -- When available, QSM message #
　　--explaining why not possible or more
　　--details when enabled.
　　 MSGTXT VARCHAR(2000),-- Text associated with MSGNO.
　　 SEQ NUMBER);
　　 -- Useful in ORDERBY clause when
　　--selecting from this table.
　　
　　
二．Explain_mview 使用示例
　　
　　dbms_mview.explain_mview能分析三种不同的物化视图代码，分别是：
　　1.定义的查询
　　2.一个create materialized view的语句
　　3.一个存在的物化视图
　　
　　因为物化视图在语法有一定的限制，所以在创建物化视图之前我们可以先使用explain_mview 过程来验证一下语法上的问题。如：
　　
　　SQL> execdbms_mview.explain_mview('select * from dave');
　　PL/SQL proceduresuccessfully completed.
　　
　　--然后查看mv_capabilities_table 表：
　　
　　SQL> desc mv_capabilities_table
　　Name
　　---------------------------------
　　STATEMENT_ID
　　MVOWNER
　　MVNAME
　　CAPABILITY_NAME
　　POSSIBLE
　　RELATED_TEXT
　　RELATED_NUM
　　MSGNO
　　MSGTXT
　　SEQ
　　
　　SQL> selectcapability_name,possible,msgtxt from mv_capabilities_table;
　　
　　CAPABILITY_NAME P MSGTXT
　　------------------------------ ------------------------------------------------------------------------------------
　　PCT N
　　REFRESH_COMPLETE N no primary key constraint inthe master table
　　--这里提示我们主表没有主键，
　　REFRESH_FAST N
　　REWRITE N
　　PCT_TABLE N Oracle error: seeRELATED_NUM and RELATED_TEXT for details
　　REFRESH_FAST_AFTER_INSERT N the detail table does not have amaterialized view log
　　REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERTis disabled
　　REFRESH_FAST_AFTER_ANY_DML N see the reason whyREFRESH_FAST_AFTER_ONETAB_DML is disabled
　　REFRESH_FAST_PCT N PCT is not possible on any ofthe detail tables in the materialized view
　　REWRITE_FULL_TEXT_MATCH NOracle error: see RELATED_NUM and RELATED_TEXT for details
　　REWRITE_PARTIAL_TEXT_MATCH N materialized view cannot support anytype of query rewrite
　　REWRITE_GENERAL N materialized view cannotsupport any type of query rewrite
　　REWRITE_PCT N general rewrite is notpossible or PCT is not possible on any of the detail tables
　　PCT_TABLE_REWRITE N Oracle error: see RELATED_NUMand RELATED_TEXT for details
　　--这里会显示所有不符合的地方。
　　
　　
　　SQL> create materialized view mv_daverefresh fast on demand as select * from dave;
　　create materialized view mv_dave refreshfast on demand as select * from dave
　　*
　　ERROR at line 1:
　　ORA-12014: 表 'DAVE' 不包含主键约束条件
　　--如果我们直接使用上面的语句，就会出现没有主键的错误。
　　
　　
　　我们创建一个新的物化视图，然后使用explain_mview 来验证：
　　SQL> create table anqing as select *from all_users;
　　Table created.
　　
　　
　　SQL> create materialized view mv_daverefresh force on demand as select * from anqing;
　　Materialized view created.
　　
　　SQL> exec dbms_mview.explain_mview('mv_dave');
　　PL/SQL procedure successfully completed.
　　
　　SQL> selectcapability_name,possible,msgtxt from mv_capabilities_table wheremvname='MV_DAVE';
　　
　　CAPABILITY_NAME P MSGTXT
　　------------------------------ -----------------------------------------------------------------------------------
　　PCT N
　　REFRESH_COMPLETE Y
　　REFRESH_FAST N
　　REWRITE N
　　PCT_TABLE N Oracle error: seeRELATED_NUM and RELATED_TEXT for details
　　REFRESH_FAST_AFTER_INSERT N does not meet the requirements of aprimary key mv
　　REFRESH_FAST_AFTER_ONETAB_DML N see the reason whyREFRESH_FAST_AFTER_INSERT is disabled
　　REFRESH_FAST_AFTER_ANY_DML N see the reason whyREFRESH_FAST_AFTER_ONETAB_DML is disabled
　　REFRESH_FAST_PCT N PCT is not possible on any ofthe detail tables in the materialized view
　　REWRITE_FULL_TEXT_MATCH N Oracle error: see RELATED_NUM andRELATED_TEXT for details
　　REWRITE_FULL_TEXT_MATCH N query rewrite is disabled on thematerialized view
　　REWRITE_PARTIAL_TEXT_MATCH N materialized view cannot support anytype of query rewrite
　　REWRITE_PARTIAL_TEXT_MATCH N query rewrite is disabled on thematerialized view
　　REWRITE_GENERAL N materialized view cannotsupport any type of query rewrite
　　REWRITE_GENERAL N query rewrite is disabled onthe materialized view
　　REWRITE_PCT N general rewrite is not possible or PCT isnot possible on any of the detail tables
　　PCT_TABLE_REWRITE N Oracle error: see RELATED_NUMand RELATED_TEXT for details
　　
　　17 rows selected.
　　
　　--这里就ok了。
　　
　　
　　
　　
　　-------------------------------------------------------------------------------------------------------
　　版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
　　Skype:  tianlesoftware
　　QQ: tianlesoftware@gmail.com
　　Email:  tianlesoftware@gmail.com
　　Blog: http://www.tianlesoftware.com
　　Weibo:  http://weibo.com/tianlesoftware
　　Twitter: http://twitter.com/tianlesoftware
　　Facebook: http://www.facebook.com/tianlesoftware
　　Linkedin: http://cn.linkedin.com/in/tianlesoftware
　　
　　
　　-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
　　DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)
　　DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
　　DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940