dual 表引起create materialized view 的ora-1422

关于dual表的链接

http://songhefei.itpub.net/post/7281/216328

http://songhefei.itpub.net/post/7281/463102

create materilized view 时报 ora-1422错误

原来前两天测试dual表时候,insert 了一条数据,而没有删除

select count(*) from dual;

竟然返回了2

真是让人苦笑不得

查看create materialized view 的10046 trace file

其中有一段

PARSING IN CURSOR #3 len=21 dep=0 uid=61 oct=3 lid=61 tim=2832974607 hv=2888538493 ad='66a7e540'
select 'x' from dual
END OF STMT
PARSE #3:c=0,e=545,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2832974599
EXEC #3:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2832982838
FETCH #3:c=0,e=58,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2832985148
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================

附: ORACLE关于DUAL表不同寻常特性的解释
There is internalized code that makes this happen. Code checks that ensurethat a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product.
The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1).
This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other
prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception.
So DUAL should ALWAYS have 1 and only 1 row
*/

Trackback URL: http://blog.itpub.net//trackback.php?id=464539