ORA-600(999)错误（二）

今天后台出现了很多ORA-600，其中第一个错误参数为999。

这个问题在METALINK上没有找到类似的说明。

这篇进一步深入研究数据库中缺少了哪些信息造成了这个问题。

关于错误问题产生的原因参考：ORA-600(999)错误（一）：http://yangtingkun.itpub.net/post/468/281181

上一篇文章简单讨论了为什么会出现这个问题。这里打算分析一下这个数据库缺少了哪些数据字典信息，导致了问题的产生。

首先，虽然Oracle缺少了部分信息，但是Oracle仍然认为这个对象是物化视图，而且物化视图的刷新一直都是正常的。

看来要寻找问题产生的原因首先必须从错误入手。

对于正常的物化视图表，Oracle在ALTER TABLE RENAME时会报错，而对于这个数据库，则可以成功，说明在ALTER TABLE RENAME操作执行时，进行了某种检查。

寻找问题最简单的方法是进行SQL_TRACE，然后进行比较：

SQL> CREATE TABLE T_RENAME (ID NUMBER);

表已创建。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> ALTER TABLE T_RENAME RENAME TO T_RENAMED;

表已更改。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> DISC从 Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production 断开
SQL> CONN TEST/TEST@TESTZJ已连接。
SQL> DROP TABLE T_RENAMED;

表已删除。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> ALTER TABLE MV_T RENAME TO T_MV;
ALTER TABLE MV_T RENAME TO T_MV
*第 1 行出现错误:
ORA-32318: cannot rename a materialized view

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

将得到的两个TRACE信息进行比对：

*** 2007-04-06 12:25:47.625
*** SESSION ID:(37.124) 2007-04-06 12:25:47.596
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=60 oct=42 lid=60 tim=1148274948853089 hv=4177740527 ad='78ec71e0'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #1:c=10000,e=116,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1148274948824465
*** 2007-04-06 12:26:07.649
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=60 oct=15 lid=60 tim=1148274968407963 hv=4141242299 ad='78ec6db8'
ALTER TABLE T_RENAME RENAME TO T_RENAMED
END OF STMT
PARSE #1:c=0,e=614,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1148274968407947
=====================
PARSING IN CURSOR #2 len=256 dep=1 uid=0 oct=47 lid=0 tim=1148274968411117 hv=701589132 ad='735961d8'
BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type = 'TABLE' AND sys.dbms_cdc_publish.active > 0
THEN
sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,dictionary_obj_name,sysevent);
END IF;
END;
END OF STMT
PARSE #2:c=10000,e=2954,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1148274968411112
=====================
PARSING IN CURSOR #18 len=43 dep=2 uid=0 oct=3 lid=0 tim=1148274968411517 hv=1600975027 ad='734ee3a0'
SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM$
END OF STMT
EXEC #18:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1148274968411511
FETCH #18:c=0,e=123,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1148274968411726
EXEC #2:c=0,e=493,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1148274968411791
=====================
PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=1148274968412570 hv=2703824309 ad='72f8687c'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #2:c=0,e=538,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968412564
EXEC #2:c=0,e=361,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968413105
FETCH #2:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968413193
=====================
PARSING IN CURSOR #3 len=175 dep=1 uid=0 oct=3 lid=0 tim=1148274968413748 hv=3073477137 ad='72ec2e18'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# order by o.obj#
END OF STMT
PARSE #3:c=0,e=470,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968413742
EXEC #3:c=0,e=319,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968414165
FETCH #3:c=0,e=58,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968414253
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1 r=0 w=0 time=56 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=1 r=0 w=0 time=22 us)'
STAT #3 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1 r=0 w=0 time=20 us)'
=====================
PARSING IN CURSOR #4 len=116 dep=2 uid=0 oct=3 lid=0 tim=1148274968415430 hv=431456802 ad='72f837a0'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #4:c=0,e=990,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1148274968415425
EXEC #4:c=0,e=162,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1148274968415718
FETCH #4:c=0,e=57,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1148274968415813
STAT #3 id=4 cnt=0 pid=3 pos=1 obj=82 op='TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 r=0 w=0 time=19 us)'
STAT #3 id=5 cnt=0 pid=4 pos=1 obj=130 op='INDEX RANGE SCAN I_TRIGGER1 (cr=1 r=0 w=0 time=17 us)'
STAT #3 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=0 us)'
=====================
PARSING IN CURSOR #3 len=310 dep=1 uid=0 oct=3 lid=0 tim=1148274968416669 hv=412513799 ad='734c5d60'
select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# and decode(:3,0,0,o.type#)=:3
END OF STMT
PARSE #3:c=0,e=534,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968416664
EXEC #3:c=0,e=427,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968417248
FETCH #3:c=0,e=62,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968417352
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=3 r=0 w=0 time=60 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=3 r=0 w=0 time=60 us)'
STAT #3 id=3 cnt=0 pid=2 pos=1 obj=96 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 r=0 w=0 time=58 us)'
STAT #3 id=4 cnt=0 pid=3 pos=1 obj=128 op='INDEX RANGE SCAN I_DEPENDENCY2 (cr=3 r=0 w=0 time=56 us)'
STAT #3 id=5 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=6 cnt=0 pid=5 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=7 cnt=0 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=0 us)'
STAT #3 id=8 cnt=0 pid=7 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=0 us)'
=====================
PARSING IN CURSOR #3 len=34 dep=1 uid=0 oct=7 lid=0 tim=1148274968417957 hv=3342731932 ad='7338c718'
delete from idl_ub1$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=262,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968417952
EXEC #3:c=0,e=214,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968418282
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=24 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=120 op='INDEX RANGE SCAN I_IDL_UB11 (cr=2 r=0 w=0 time=21 us)'
=====================
PARSING IN CURSOR #3 len=35 dep=1 uid=0 oct=7 lid=0 tim=1148274968418645 hv=2114496667 ad='7338b1b4'
delete from idl_char$ where obj#=:1
END OF STMT
PARSE #3:c=10000,e=230,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968418640
EXEC #3:c=0,e=174,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968418927
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=21 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=121 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 r=0 w=0 time=18 us)'
=====================
PARSING IN CURSOR #3 len=34 dep=1 uid=0 oct=7 lid=0 tim=1148274968419296 hv=3403214913 ad='7338a648'
delete from idl_ub2$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=238,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968419291
EXEC #3:c=0,e=177,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968419583
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=19 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=122 op='INDEX RANGE SCAN I_IDL_UB21 (cr=2 r=0 w=0 time=17 us)'
=====================
PARSING IN CURSOR #3 len=34 dep=1 uid=0 oct=7 lid=0 tim=1148274968419935 hv=1006630772 ad='73386c80'
delete from idl_sb4$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=222,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968419930
EXEC #3:c=0,e=172,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968420214
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=23 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=123 op='INDEX RANGE SCAN I_IDL_SB41 (cr=2 r=0 w=0 time=21 us)'
=====================
PARSING IN CURSOR #3 len=32 dep=1 uid=0 oct=7 lid=0 tim=1148274968420573 hv=4144490151 ad='72e5ed0c'
delete from error$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=231,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968420568
EXEC #3:c=0,e=162,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274968420843
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=18 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=125 op='INDEX RANGE SCAN I_ERROR1 (cr=2 r=0 w=0 time=16 us)'
=====================
PARSING IN CURSOR #3 len=32 dep=1 uid=0 oct=7 lid=0 tim=1148274968421268 hv=3821382080 ad='72df7da4'
delete from obj$ where obj# = :1
END OF STMT
PARSE #3:c=0,e=249,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968421263
EXEC #3:c=0,e=746,p=0,cr=2,cu=6,mis=0,r=1,dep=1,og=4,tim=1148274968422124
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE (cr=2 r=0 w=0 time=593 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 r=0 w=0 time=16 us)'
XCTEND rlbk=0, rd_only=0
=====================
PARSING IN CURSOR #3 len=205 dep=1 uid=0 oct=2 lid=0 tim=1148274968423300 hv=2296808019 ad='72b3a1a0'
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
END OF STMT
PARSE #3:c=0,e=478,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148274968423294
EXEC #3:c=0,e=543,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=1148274968424021
EXEC #1:c=20000,e=17344,p=0,cr=25,cu=12,mis=0,r=0,dep=0,og=4,tim=1148274968425405
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=60 oct=15 lid=60 tim=1148274976538619 hv=1179767198 ad='78ea1134'
ALTER TABLE MV_T RENAME TO T_MV
END OF STMT
PARSE #1:c=0,e=325,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1148274976538612
=====================
PARSING IN CURSOR #2 len=256 dep=1 uid=0 oct=47 lid=0 tim=1148274976538917 hv=701589132 ad='735961d8'
BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type = 'TABLE' AND sys.dbms_cdc_publish.active > 0
THEN
sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,dictionary_obj_name,sysevent);
END IF;
END;
END OF STMT
PARSE #2:c=0,e=149,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148274976538914
EXEC #18:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1148274976539235
FETCH #18:c=0,e=80,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1148274976539341
EXEC #2:c=0,e=320,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1148274976539401
EXEC #1:c=0,e=838,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=1148274976539545
ERROR #1:err=32318 tim=1619240605
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=60 oct=42 lid=60 tim=1148274984441814 hv=855351039 ad='78ea7c04'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #1:c=0,e=475,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1148274984441795
EXEC #1:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1148274984442039

上面是正常的ALTER TABLE RENAME的TRACE信息，在来看物化视图的RENAME的TRACE信息：

*** 2007-04-06 12:33:55.912
*** SESSION ID:(32.116) 2007-04-06 12:33:55.912
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=60 oct=42 lid=60 tim=1148275425695767 hv=4177740527 ad='78ec71e0'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1148275425695289
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=60 oct=15 lid=60 tim=1148275427847577 hv=1179767198 ad='78ea1134'
ALTER TABLE MV_T RENAME TO T_MV
END OF STMT
PARSE #1:c=0,e=382,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1148275427847571
=====================
PARSING IN CURSOR #2 len=256 dep=1 uid=0 oct=47 lid=0 tim=1148275427848522 hv=701589132 ad='735961d8'
BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type = 'TABLE' AND sys.dbms_cdc_publish.active > 0
THEN
sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,dictionary_obj_name,sysevent);
END IF;
END;
END OF STMT
PARSE #2:c=0,e=780,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148275427848517
=====================
PARSING IN CURSOR #3 len=43 dep=2 uid=0 oct=3 lid=0 tim=1148275427848948 hv=1600975027 ad='734ee3a0'
SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM$
END OF STMT
EXEC #3:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1148275427848944
FETCH #3:c=0,e=115,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1148275427849150
EXEC #2:c=0,e=514,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1148275427849219
=====================
PARSING IN CURSOR #2 len=343 dep=1 uid=0 oct=3 lid=0 tim=1148275427850356 hv=2367942048 ad='72f796cc'
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spare6 from tab$ where obj#=:1
END OF STMT
PARSE #2:c=0,e=988,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1148275427850350
EXEC #2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1148275427851062
FETCH #2:c=0,e=106,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1148275427851208
EXEC #1:c=0,e=3694,p=0,cr=7,cu=0,mis=0,r=0,dep=0,og=4,tim=1148275427851355
ERROR #1:err=32318 tim=1619286819
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=60 oct=42 lid=60 tim=1148275430433878 hv=855351039 ad='78ea7c04'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #1:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1148275430433871
EXEC #1:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1148275430434045

显然，Oracle在执行完下面这个SQL后报错：

select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),
nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),
nvl(spare1,0),nvl(spare2,0),spare4,spare6
from tab$
where obj#=:1

说明Oracle的在执行完这个SQL后，就确定是否是物化视图了。

SQL> SELECT FLAGS,PROPERTY FROM TAB$ WHERE OBJ# IN (45549, 43031);

FLAGS PROPERTY
---------- ----------
1 536870912
262145 637534208

对比物化视图和普通表，去掉二者完全相同的信息，得到上面的结果。对于物化视图和表，只有FLAGS和PROPERTY有所区别。

如果对数据字典信息比较熟悉，其实已经可以断定是PROPERTY字段包含了关键信息，如果不熟悉，也没有关系。

下面再进行诊断：找一个没有问题的物化视图对象和这个出现问题的站点的物化视图信息进行比较，看看问题出在哪里：

正常的物化视图：

SQL> SELECT FLAGS,PROPERTY FROM TAB$ WHERE OBJ# IN (46580);

FLAGS PROPERTY
---------- ----------
262673 637534208

而问题物化视图：

SQL> SELECT FLAGS,PROPERTY FROM TAB$ WHERE OBJ# IN (32907);

FLAGS PROPERTY
---------- ----------
262673 570425344

这下问题明显了，错误出现在PROPERTY字段。

SQL> SELECT 637534208-570425344 FROM DUAL;

637534208-570425344
-------------------
67108864

而丢失的这个67108864信息，正好是物化视图表的信息。可以参考DBA_LOGSTDBY_UNSUPPORTED视图的定义：

SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_LOGSTDBY_UNSUPPORTED';

TEXT
------------------------------------------------------------------------------
select tc.owner, tc.table_name, tc.column_name, tc.data_type
from
(select u.name owner, o.name table_name, c.name column_name, c.type#,
o.obj#, t.property tprop, t.flags tflags, nvl(s.spare1,0) segspare1,
/* BEGIN SECTION 1 COMMON CODE: LOGSTDBY_SUPPORT - DBA_LOGSTDBY_UNSUPPORTED */
(case
when u.name = 'SYS'
or u.name = 'SYSTEM'
or u.name = 'OUTLN'
or u.name = 'DBSNMP'
or bitand(o.flags,
2 /* temporary object */
+ 4 /* system generated object */
+ 16 /* secondary object */
+ 32 /* in-memory temp table */
+ 128 /* dropped table (RecycleBin) */
) != 0
or bitand(t.flags,
262144 /* 0x00040000 Summary Container Table, MV */
+ 134217728 /* 0x08000000 in-memory temporary table */
) != 0
or bitand(t.property,
512 /* 0x00000200 iot OVeRflow segment */
+ 8192 /* 0x00002000 nested table */
+ 131072 /* 0x00020000 table is used as AQ queue table */
+ 4194304 /* 0x00400000 global temporary table */
+ 8388608 /* 0x00800000 session-specific temporary table */
+ 33554432 /* 0x02000000 Read Only Materialized View */
+ 67108864 /* 0x04000000 Materialized View table */
+ 134217728 /* 0x08000000 Is a Sub object */
+ 2147483648 /* 0x80000000 eXternal TaBle */
) != 0
or exists /* MVLOG table */
(select 1
from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name)
then -1
when bitand(t.property,
64 /* 0x00000040 IOT table */
+ 128 /* 0x00000080 IOT with row overflow */
+ 256 /* 0x00000100 IOT with row clustering */
) != 0
or bitand(t.flags,
268435456 /* 0x10000000 IOT with Phys Rowid/mapping tab */
+ 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */
) != 0
or bitand(t.property, 262208) = 262208 /* 0x40+0x40000 IOT + user LOB */
or bitand(t.property, 2112) = 2112 /* 0x40+0x800 IOT + internal LOB */
or /* IOT with Row Movement */
(bitand(t.property, 64) = 64 and bitand(t.flags, 131072) = 131072)
or /* Compression */
(bitand(nvl(s.spare1,0), 2048) = 2048 and bitand(t.property, 32) != 32)
or o.oid$ is not null
/* END SECTION 1 COMMON CODE */
or
/* BEGIN SECTION 2 COMMON CODE: LOGSTDBY_SUPPORT - DBA_LOGSTDBY_UNSUPPORTED */
(c.type# not in (
1, /* VARCHAR2 */
2, /* NUMBER */
12, /* DATE */
96, /* CHAR */
113, /* BLOB */
180, /* TIMESTAMP (..) */
181, /* TIMESTAMP(..) WITH TIME ZONE */
182, /* INTERVAL YEAR(..) TO MONTH */
183, /* INTERVAL DAY(..) TO SECOND(..) */
231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */
and (c.type# != 112 /* CLOB not NCLOB */
or (c.type# = 112 and c.charsetform = 2))
and (c.type# != 23 /* RAW not RAW OID */
or (c.type# = 23 and bitand(c.property, 2) = 2)))
/* END SECTION 2 COMMON CODE */
or (c.segcol# = 0 or /* virtual column, segcol#=0 is bad */
bitand(c.property, 32768) = 32768) /* unused col, bad */
then 0 else 1 end) gensby
from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c
where o.owner# = u.user#
and o.obj# = t.obj#
and o.obj# = c.obj#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.obj# = o.obj#
and (c.segcol# = 0 or /* virtual column, segcol#=0 is bad */
bitand(c.property, 32768) = 32768 or /* unused col, bad */
bitand(c.property, 32) != 32) /* not hidden */
) l, dba_tab_cols tc
where l.owner = tc.owner
and l.table_name = tc.table_name
and l.column_name = tc.column_name
and l.gensby = 0

视图定义中注释清晰的说明67108864是物化视图表信息。因此这就是导致这个问题产生的真正原因。在全库导入过程中，表的导入和物化视图导入是分开的，因此，Oracle丢失了表上的这个信息。导致了问题的产生。

因此，对于这种负责环境，还是建议采用迁移后重新建立的方式，采用全库EXP/IMP的方式很容易造成问题。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-69245/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-69245/