Oracle 10g中dba_objects无法查询出lob字段索引

说明:
在一次expdp跨版本迁移时(10g->11g),迁移完成后,在进行目标端与源端对象数量校验时.发现目标端索引数量比源端索引数量多。

校验语句如下:
select OWNER,OBJECT_TYPE,count(*) from dba_objects where owner in ('ANONYMOUS','BAJK','BATJ','EMR','EMR3','EMRJK','ZHLIS','JYK','XUEYE','HISBAK','YLSBGL','PDJH','DSG','SCOTT','MGMT_VIEW','MDDATA','TSMSYS','ZJHIS','HIYB','SIIMHIS','SIIM')
group by OWNER,OBJECT_TYPE order by owner;

截取部分:
源端:
OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
EMR                            INDEX                      295
EMR                            LOB                          5
ZJHIS                          INDEX                     1385
ZJHIS                          LOB                          5
JYK                            INDEX                      321
JYK                            LOB                         10
EMR3                           INDEX                      396
EMR3                           LOB                         60

目标端:
OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
EMR                            INDEX                      300
EMR                            LOB                          5
ZJHIS                          INDEX                     1390
ZJHIS                          LOB                          5
JYK                            INDEX                      331
JYK                            LOB                         10
EMR3                           INDEX                      456
EMR3                           LOB                         60

发现目标端索引数量比源端索引数量多.

排查问题流程:

确定多出的索引:
校验1:
源端目标端通过dba_index校验:
SPOOL E:\test\yuanduan.LOG
select  table_name,count(*) from dba_indexes where owner='ZJHIS' group by table_name order by 1;
SPOOL OFF

SPOOL /home/oracle/mubiaoduan.LOG
select  table_name,count(*) from dba_indexes where owner='ZJHIS' group by table_name order by 1;
SPOOL OFF

经校验,发现通过dba_indexes索引数量一致


校验2:
SPOOL E:\test\yuanduan.LOG
col object_name for a40;
select  object_name,count(*) from dba_objects where owner='ZJHIS' and object_type='INDEX' group by object_name order by 1;
SPOOL OFF

SPOOL /home/oracle/mubiaoduan.LOG
col object_name for a40;
select  object_name,count(*) from dba_objects where owner='ZJHIS' and object_type='INDEX' group by object_name order by 1;
SPOOL OFF

经校验,确定多出索引为lob字段索引.
SYS_IL0000020483C00058$$
SYS_IL0000020649C00002$$
SYS_IL0000020687C00004$$
SYS_IL0000020709C00002$$
SYS_IL0000020709C00003$$

lob index必然是系统自动创建的，其遵循下面的规则：
SYS_IL(10 digit object_id)C(5 digit col#)$$

根据LOB索引进行查询表以及表列信息:
目标端:
select owner,table_name,column_name,segment_name,index_name from  dba_lobs where index_name='SYS_IL0000020709C00003$$';
OWNER                          TABLE_NAME         COLUMN_NAME     SEGMENT_NAME               INDEX_NAME
-------------------- -------------------------  ----------------  --------------------       ---------------------------
ZJHIS                          YB_JYRZXX_EXP       JYJGXX         SYS_LOB0000020709C00003$$  SYS_IL0000020709C00003$$


源端查询该表信息:
desc ZJHIS.YB_JYRZXX_EXP
名称                                      是否为空? 类型
----------------------------------------- -------- ------------------------
RZJLXH                                    NOT NULL NUMBER(10)
JYRCXX                                             BLOB
JYJGXX                                             BLOB

源端通过dba_ind_columns视图查询为空
SQL>select index_name,table_name,column_name from dba_ind_columns where column_name = 'JYJGXX' and table_name='YB_JYRZXX_EXP';
no now select 

源端通过dba_indexes查是可以查询出来的
SQL> select index_name,index_type,table_name from dba_indexes where table_name='YB_JYRZXX_EXP';
SYS_IL0000240400C00002$$       LOB                         YB_JYRZXX_EXP
SYS_IL0000240400C00003$$       LOB                         YB_JYRZXX_EXP
PK_YB_JYRZXX_EXP               NORMAL                      YB_JYRZXX_EXP

发现在源端也是存在该索引.那么为什么查询会多出lob字段数量的索引.

结论:
多出索引的问题关键点出现在10g通过dba_objects查询不到LOB类型索引,而11g可以通过dba_objects可以查询出LOB类型索引 最终原因为10g与11g dba_objects系统表的引用基表的定义方式不同.