创建物化视图报ORA-6512不一定是bug
您知道在 oracle 上创建物化视图时，需要注意到什么吗？在出错的时候，该如何调试吗？
我最近遇到这样一个问题。在创建一个每天一次、全量更新物化视图时出错了。
报的错误是 ORA-6512 ，是 关于SYS.DBMS_SNAPSHOT_UTL 包的。
这是系统包的错误，这会是Oracle bug吗？
物化视图脚本很简单，同步异地的一个数据库中的一张表到本地数据库中来，属于常见的数据同步操作。
异地和本地的数据库版本都是 Oracle 10.2.0.3 。
CREATE MATERIALIZED VIEW V_JW_COURSETIMETABLE
REFRESH COMPLETE ON DEMAND
START WITH TO_DATE('28-05-2012 21:47:56', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1
AS
SELECT WID AS WID, KCM as KCM, JXBH AS JXBH, KKNF AS KKNF, KKXQM AS KKXQM, XQ AS XQ, JS AS JS, ZS AS ZS, JSGH AS JSGH
FROM USR_GXSJ.V_JW_COURSETIMETABLE@lk_rs_dpstar
where
(KKNF = '2011' AND KKXQM = '1') OR (KKNF = '2011' AND KKXQM = '2')
还有一点很郁闷。该物化视图之前是创建成功的，现在是删除掉重建就不行了。
（miki 西游的文档：原文链接链接 : http://mikixiyou.iteye.com/blog/1543973 转载请著明出处和作者）
1.分析
我们首先检查物化视图创建语法，完全没有看出来错误。
我们再核实其中的 SELECT 操作，也能正常执行出结果。
之前这个物化视图视图是存在的，只是删除掉略作字段调整而重建一下而已。
现在，我们该如何去分析和解决这个问题呢？
查官方文档：
在 oracle metalink 查阅到这些信息，有一个 bug5015547 ，他的描述信息同我们的错误完全一致。文档为 Bug 5015547 : CANNOT CREATE A MATERIALIZED VIEW OVER A DATABASE LINK OR A-942
文档中的信息如下：
Bug 5015547 : CANNOT CREATE A MATERIALIZED VIEW OVER A DATABASE LINK ORA-942
------------------
Security setup is :
Local side :
user_d - materialized view owner;
Remote side :
user_a - table owner;
user_b - has view on table in user_a's schema
user_c - has select privs on view in user_b's schema.
connect User_D/User_D
drop materialized view User_D.Table1;
CREATE MATERIALIZED VIEW User_D.Table1 REFRESH WITH ROWID AS SELECT * FROM
the statement which is failing is :
ORA-942: table or view does not exist
ORA-6512: at "SYS.DBMS_SNAPSHOT_UTL", line 1543
ORA-2063: preceding 2 lines from TARMM
因为有如此类似的 bug 信息，所以决定先安装一下补丁包，试试看能否解决掉这个问题。
（注，这里开始走了弯路，不相信自己判断，盲从官方文档）
2.解决过程
2.1安装补丁包
这是一个 RAC 架构的数据库，因此需要在每个节点上依次安装补丁包 5015547
安装过程如下：大家可以参考一下如何在 RAC 下依次安装小补丁包。
一个节点一个节点地关闭数据库实例，ASM 实例，监听器应用
/data/oracle/home/5015547@edbrac3=>+ASM3$opatch apply -local
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /opt/app/oracle/product/10.2.0/db_1
Central Inventory : /opt/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version : 10.2.0.3.0
OUI location : /opt/app/oracle/product/10.2.0/db_1/oui
Log file location : /opt/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2012-05-28_13-11-46PM.log
ApplySession applying interim patch '5015547' to OH '/opt/app/oracle/product/10.2.0/db_1'
Invoking fuser to check for active processes.
Invoking fuser on "/opt/app/oracle/product/10.2.0/db_1/bin/oracle"
You selected -local option, hence OPatch will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/app/oracle/product/10.2.0/db_1')
Is the local system ready for patching?
Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5015547' for restore. This might take a while...
^[Backing up files affected by the patch '5015547' for rollback. This might take a while...
Patching component oracle.rdbms, 10.2.0.3.0...
Updating archive file "/opt/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kkzd.o"
ApplySession adding interim patch '5015547' to inventory
Verifying the update...
Inventory check OK: Patch ID 5015547 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 5015547 are present in Oracle Home.
Running make for target ioracle
The local system has been patched and can be restarted.
终于执行安装完毕。
但是创建还是报同样的错误。
下面怎么办？
2.2使用 10046 event 分析
我们还有 10046 event 工具，可以进一步分析问题内部更详细的报错信息。
alter session set events '10046 trace name context forever, level 10';
在 udump 下，找到最新的 trc 文件，就是 10046 event 生成的日志文件。在文件中，我们发现了下面有用的信息。
=====================
PARSING IN CURSOR #3 len=152 dep=2 uid=137 oct=47 lid=137 tim=15360746465623 hv=3730321282 ad='d4484170'
begin sys.dbms_snapshot_utl.get_pk_constraint_info@LK_RS_DPSTAR_FOR_USR_RS_APP (:mowner, :master, :pknm, :pktyp, :pkcols, :idxnm, :idxcols); end;
END OF STMT
PARSE #3:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=15360746465621
WAIT #3: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=320 tim=15360746465766
WAIT #3: nam='SQL*Net message from dblink' ela= 519 driver id=1413697536 #bytes=4 p3=0 obj#=320 tim=15360746466303
WAIT #3: nam='SQL*Net break/reset to dblink' ela= 22 driver id=1413697536 break?=0 p3=0 obj#=320 tim=15360746466342
EXEC #3:c=0,e=712,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=15360746466429
ERROR #3:err=942 tim=1572940438
=====================
PARSE ERROR #1:len=248 dep=1 uid=137 oct=3 lid=137 tim=15360746466502 err=942
SELECT WID AS WID, KCM as KCM, JXBH AS JXBH, KKNF AS KKNF, KKXQM AS KKXQM, XQ AS XQ, JS AS JS, ZS AS ZS, JSGH AS JSGH
FROM V_JW_COURSETIMETABLE@LK_RS_DPSTAR_for_usr_rs_app
where
(KKNF = '2011' AND KKXQM = '1') OR (KKNF = '2011' AND KKXQM = '2')
EXEC #2:c=0,e=22751,p=0,cr=4,cu=11,mis=0,r=0,dep=0,og=1,tim=15360746466604
ERROR #2:err=942 tim=1572940438
报错的系统包是
begin sys.dbms_snapshot_utl.get_pk_constraint_info@LK_RS_DPSTAR_FOR_USR_RS_APP (:mowner, :master, :pknm, :pktyp, :pkcols, :idxnm, :idxcols); end;
我们在 sqlplus 界面中，只看到 sys.dbms_snapshot_utl 报错，其实真正报错的是存储过程 get_pk_constraint_info 。
从这个过程名称可以看出，这是再校验基础表的主键字段出错。
我们物化视图脚步中，没有指明是使用 rowid 还是 primary key 方式遍历数据。默认使用 primary key 。
这里很可能基表上没有了主键约束。
2.3处理方法
我暂时将物化视图脚步做了修改，增加 with rowid 子句，创建成功。具体脚步如下：
CREATE MATERIALIZED VIEW V_JW_COURSETIMETABLE
REFRESH COMPLETE ON DEMAND
START WITH TO_DATE('28-05-2012 21:47:56', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1
with rowid
AS
SELECT WID AS WID, KCM as KCM, JXBH AS JXBH, KKNF AS KKNF, KKXQM AS KKXQM, XQ AS XQ, JS AS JS, ZS AS ZS, JSGH AS JSGH
FROM USR_GXSJ.V_JW_COURSETIMETABLE@lk_rs_dpstar
where
(KKNF = '2011' AND KKXQM = '1') OR (KKNF = '2011' AND KKXQM = '2')
更值得推荐的方法是使用采用主键字段做刷新。在基础表上创建主键约束。
3.总结
在涉及到跨图的物化视图同步数据的开发操作时，一个最基本的要求时，基础表上有主键约束。
再深入一下，所有的表如无明确要求，都应该具有主键约束。
上一篇： Oracle 11g2的监听器配置