10g逻辑导入到11g后，查看dba_objects多了对象

原创黄宸宁 2014-02-23

1121

周六客户做数据迁移，同事发现10g数据迁移到11g后，通过dba_objects查看对象时，指定的业务账号下多了不少对象（测试工作也是该同事前两天做的，但是未发现这个问题，有点奇怪，难道是这两天多了对象了？），因为客户资料比较敏感，所以未记录实际的情况，只能在自己的电脑上重现下当时的情况。
当时的情况是发现多出的对象集中在其中两个业务用户上，选择了一个对象较少的用户进行分析，发现多出的对象名都为：SYS_IL打头的对象，感觉是含有lob字段表的对象，因为在创建含有LOB字段的表时，会随Lobsegment 自动创建SYS_IL前缀命名的LOB index对象，在10g的dba_objects视图中是没显示这部分LOB index对象的，但是在11g中却有显示。
下面是场景重现：
1.新创建用户

10g：
create user objtest identified by oracle;
grant resource,create session to objtest;
21:13:26 SYS@HCN>
21:13:26 SYS@HCN>create user objtest identified by oracle default tablespace HCNTBS;
User created.
21:13:28 SYS@HCN>
21:14:02 SYS@HCN>
21:14:02 SYS@HCN>grant resource,create session to objtest;
Grant succeeded.
21:14:03 SYS@HCN>
11g:
SQL> 
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> 
SQL> create user objtest identified by oracle default tablespace HCNTBS;
User created.
SQL> grant resource,create session to objtest;
Grant succeeded.
SQL> 
SQL>

2.创建含有LOB字段的对象，并插入数据

10g：
21:18:30 SYS@HCN>
21:18:30 SYS@HCN>conn objtest/oracle
Connected.
21:18:36 OBJTEST@HCN>
21:18:37 OBJTEST@HCN>
21:18:37 OBJTEST@HCN>CREATE TABLE objtest.test
21:18:41   2  ( author varchar2(30), 
21:18:41   3        article_name varchar2(50),
21:18:41   4        article_date date,
21:18:41   5        article_data clob
21:18:41   6  )
21:18:41   7  LOB (article_data) STORE AS
21:18:41   8  (TABLESPACE HCNTBS 
21:18:41   9   disable storage in row
21:18:41  10   CHUNK 16k
21:18:41  11   NOCACHE);
Table created.
21:18:42 OBJTEST@HCN>
21:18:43 OBJTEST@HCN>
21:18:43 OBJTEST@HCN>insert into test values('aaa','aaaa',sysdate,'aaaOracle9ibbbd');
1 row created.
21:18:48 OBJTEST@HCN>insert into test values('bbb','bbbb',sysdate,'ccsdOracle9iOracle9iOracle9idafe');
1 row created.
21:18:48 OBJTEST@HCN>insert into test values('ccc','cccc',sysdate,'dfewOracle9iOracle9iOracle9iOracle9icvae');
1 row created.
21:18:48 OBJTEST@HCN>insert into test values('ddd','dddd',sysdate,'daewfOracle9idaewfOracle9idaewfOracle9idaewfOracle9i');
1 row created.
21:18:49 OBJTEST@HCN>
21:18:49 OBJTEST@HCN>commit;
Commit complete.
21:18:51 OBJTEST@HCN>
21:18:51 OBJTEST@HCN>
11g：
SQL> 
SQL> conn objtest/oracle
Connected.
SQL> show user
USER is "OBJTEST"
SQL> 
SQL> CREATE TABLE objtest.test
  2  ( author varchar2(30), 
  3        article_name varchar2(50),
  4        article_date date,
  5        article_data clob
  6  )
  7  LOB (article_data) STORE AS
  8  (TABLESPACE HCNTBS 
  9   disable storage in row
 10   CHUNK 16k
 11   NOCACHE);
Table created.
SQL> insert into test values('aaa','aaaa',sysdate,'aaaOracle9ibbbd');
1 row created.
SQL> insert into test values('bbb','bbbb',sysdate,'ccsdOracle9iOracle9iOracle9idafe');
1 row created.
SQL> insert into test values('ccc','cccc',sysdate,'dfewOracle9iOracle9iOracle9iOracle9icvae');
1 row created.
SQL> insert into test values('ddd','dddd',sysdate,'daewfOracle9idaewfOracle9idaewfOracle9idaewfOracle9i');
1 row created.
SQL> 
SQL> commit;
Commit complete.
SQL> 
SQL>

3.查看dba_object中objtest用户的对象

10g：
21:20:58 SYS@HCN>
21:20:59 SYS@HCN>set lines 200
21:21:01 SYS@HCN>col object_name for a40
21:21:13 SYS@HCN>select owner,object_name,object_type from dba_objects where owner='OBJTEST';
OWNER                          OBJECT_NAME                              OBJECT_TYPE
------------------------------ ---------------------------------------- -------------------
OBJTEST                        TEST                                     TABLE
OBJTEST                        SYS_LOB0000053738C00004$$                LOB
21:21:21 SYS@HCN>
11g：
SQL> 
SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> 
SQL> set lines 200
SQL> col object_name for a40
SQL> select owner,object_name,object_type from dba_objects where owner='OBJTEST';
OWNER                          OBJECT_NAME                              OBJECT_TYPE
------------------------------ ---------------------------------------- -------------------
OBJTEST                        SYS_IL0000076847C00004$$                 INDEX   ---11g多出显示的对象
OBJTEST                        TEST                                     TABLE
OBJTEST                        SYS_LOB0000076847C00004$$                LOB
SQL>

4.查看10g与11g的dba_objects视图的定义
10g：

CREATE OR REPLACE VIEW DBA_OBJECTS
(owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary)
AS
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;

11g:

CREATE OR REPLACE VIEW DBA_OBJECTS
(owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary, namespace, edition_name)
AS
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER', 101, 'DESTINATION',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       o.namespace,
       o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type# !=  10 /* NON-EXISTENT */
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;

可以看到10g与11g在where条件中出现的明显变化：
10g：

from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'

11g：

from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type# !=  10 /* NON-EXISTENT */
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0

11g中查询的不再直接是基表obj$，而是变成了sys."_CURRENT_EDITION_OBJ" ，下面看看它的定义：

create or replace view _current_edition_obj as
select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",
       o.spare3,
       case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or
                  bitand(u.spare1, 16) = 0) then
         null
       when (u.type# = 2) then
        (select eo.name from obj$ eo where eo.obj# = u.spare2)
       else
        'ORA$BASE'
       end
from obj$ o, user$ u
where o.owner# = u.user#
  and (   /* non-versionable object */
          (   o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
           or bitand(u.spare1, 16) = 0)
          /* versionable object visible in current edition */
       or (    o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
           and (   (u.type# <> 2 and
                    sys_context('userenv', 'current_edition_name') = 'ORA$BASE')
                or (u.type# = 2 and
                    u.spare2 = sys_context('userenv', 'current_edition_id'))
                or exists (select 1 from obj$ o2, user$ u2
                           where o2.type# = 88
                             and o2.dataobj# = o.obj#
                             and o2.owner# = u2.user#
                             and u2.type#  = 2
                             and u2.spare2 =
                                  sys_context('userenv', 'current_edition_id'))
               )
          )
      );

其实也是对obj$与user$基表做的一个处理后形成的视图。
重点在10g中排除条件之一为：
1. obj$.type# not in (1,10)
2. 如果obj$.type#=1，那么ind$.type# 必须为(1, 2, 3, 4, 6, 7, 9)
来看看10g中的lob index是不是能满足以上两个条件

21:43:25 SYS@HCN>
21:43:25 SYS@HCN>select obj#,name,type# from obj$ where name='SYS_IL0000053738C00004$$';
      OBJ# NAME                                TYPE#
---------- ------------------------------ ----------
     53740 SYS_IL0000053738C00004$$                1
21:43:28 SYS@HCN>
21:43:29 SYS@HCN>
21:43:28 SYS@HCN>
21:43:29 SYS@HCN>select obj#,type# from ind$ where obj#=53740;
      OBJ#      TYPE#
---------- ----------
     53740          8
21:45:02 SYS@HCN>

该lob index的obj$.type#为1，ind$.type#为8，是无法满足以上两个条件的，因此在10g的dba_objects中无法显示。
补充：
obj$.type#=1代表什么，从dba_objects视图定义的select部分可以看到：

decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER', 101, 'DESTINATION',
                     'UNDEFINED'),

1代表“index”，同样ind$.type#=8也能从dba_indexes的定义中查到

select u.name, o.name,
       decode(bitand(i.property, 16), 0, '', 'FUNCTION-BASED ') ||
        decode(i.type#, 1, 'NORMAL'||
                          decode(bitand(i.property, 4), 0, '', 4, '/REV'),
                      2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
                      5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
                      9, 'DOMAIN')

ind$.type#=8为LOB类型

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

10g逻辑导入到11g后，查看dba_objects多了对象

评论