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

Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。关于物化视图相关的说明参考：

https://www.cndba.cn/Dave/article/1091

在我们创建物化视图失败时，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_IDVARCHAR(30),--Client-supplied unique statement identifier

MVOWNERVARCHAR(30),-- NULLfor SELECT based EXPLAIN_MVIEW

MVNAMEVARCHAR(30),-- NULLfor SELECT based EXPLAIN_MVIEW

CAPABILITY_NAMEVARCHAR(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

https://www.cndba.cn/Dave/article/1091

--   text match rewrite

--REWRITE_GENERAL

-- 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.

POSSIBLECHARACTER(1),-- T = capabilityis possible

-- F =capability is not possible

RELATED_TEXTVARCHAR(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_NUMNUMBER,-- 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

MSGNOINTEGER,-- When available, QSM message #

--explaining why not possible or more

https://www.cndba.cn/Dave/article/1091

--details when enabled.

MSGTXTVARCHAR(2000),-- Text associated with MSGNO.

SEQNUMBER);

-- Useful in ORDERBY clause when

--selecting from this table.

二．Explain_mview使用示例

dbms_mview.explain_mview能分析三种不同的物化视图代码，分别是：

1.定义的查询

2.一个create materialized view的语句

https://www.cndba.cn/Dave/article/1091

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

https://www.cndba.cn/Dave/article/1091

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 rewritehttps://www.cndba.cn/Dave/article/1091

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

--这里会显示所有不符合的地方。

https://www.cndba.cn/Dave/article/1091

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.

https://www.cndba.cn/Dave/article/1091

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

Blog:   http://www.tianlesoftware.comhttps://www.cndba.cn/Dave/article/1091

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA总群：104207940

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle 11g