dba_segments与dba_rollback_segs查询到的rollback结果不一样（原创）

在做一个测试的时候发现从dba_segments与dba_rollback_segs中查看到的rollback segments结果不一致，如下：

dba_segments中得到的结果：
SQL> select owner,segment_name from dba_segments where segment_type = 'ROLLBACK';

OWNER                SEGMENT_NAME
-------------------- --------------------
SYS                  SYSTEM

SQL>

dba_rollback_segs中得到的结果：

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_2986795754$           UNDOTBS1                       ONLINE
_SYSSMU2_346913448$            UNDOTBS1                       ONLINE
_SYSSMU3_219912871$            UNDOTBS1                       ONLINE
_SYSSMU4_2796668278$           UNDOTBS1                       ONLINE
_SYSSMU5_1189263717$           UNDOTBS1                       ONLINE
_SYSSMU6_2463593703$           UNDOTBS1                       ONLINE
_SYSSMU7_2494227896$           UNDOTBS1                       ONLINE
_SYSSMU8_625026625$            UNDOTBS1                       ONLINE
_SYSSMU9_3163463872$           UNDOTBS1                       ONLINE
_SYSSMU10_84651485$            UNDOTBS1                       ONLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2341436089$          UNDOTBS2                       ONLINE
_SYSSMU12_3635810052$          UNDOTBS2                       ONLINE
_SYSSMU13_1220581021$          UNDOTBS2                       ONLINE
_SYSSMU14_2107515918$          UNDOTBS2                       ONLINE
_SYSSMU15_1935625349$          UNDOTBS2                       ONLINE
_SYSSMU16_3317338710$          UNDOTBS2                       ONLINE
_SYSSMU17_3412184530$          UNDOTBS2                       ONLINE
_SYSSMU18_1923051430$          UNDOTBS2                       ONLINE
_SYSSMU19_4280175001$          UNDOTBS2                       ONLINE
_SYSSMU20_1492722088$          UNDOTBS2                       ONLINE

21 rows selected.

为了找出原因，从两个视图的定义入手（从dba_views中查询）：

dba_rollback_segs的定义：

select un.name, decode(un.user#,1,'PUBLIC','SYS'),
       ts.name, un.us#, f.file#, un.block#,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(un.status$, 2, 'OFFLINE', 3, 'ONLINE',
                          4, 'UNDEFINED', 5, 'NEEDS RECOVERY',
                          6, 'PARTLY AVAILABLE', 'UNDEFINED'),
       decode(un.inst#, 0, NULL, un.inst#), un.file#
from sys.undo$ un, sys.seg$ s, sys.ts$ ts, sys.file$ f
where un.status$ != 1
  and un.ts# = s.ts#
  and un.file# = s.file#
  and un.block# = s.block#
  and s.type# in (1, 10)
  and s.ts# = ts.ts#
  and un.ts# = f.ts#
  and un.file# = f.relfile#；

dba_segments的定义：
select owner, segment_name, partition_name, segment_type, tablespace_name,
       header_file, header_block,
       decode(bitand(segment_flags, 131072), 131072, blocks,
           (decode(bitand(segment_flags,1),1,
            dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
            header_block, segment_type_id, buffer_pool_id, segment_flags,
            segment_objd, blocks), blocks)))*blocksize,
       decode(bitand(segment_flags, 131072), 131072, blocks,
           (decode(bitand(segment_flags,1),1,
            dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
            header_block, segment_type_id, buffer_pool_id, segment_flags,
            segment_objd, blocks), blocks))),
       decode(bitand(segment_flags, 131072), 131072, extents,
           (decode(bitand(segment_flags,1),1,
           dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,
           header_block, segment_type_id, buffer_pool_id, segment_flags,
           segment_objd, extents) , extents))),
       initial_extent, next_extent, min_extents, max_extents, pct_increase,
       freelists, freelist_groups, relative_fno,
       decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys_dba_segs;
而从dba_objects查询得知sys_dba_segs还是一个视图，从而查看sys_dba_segs的定义：
select NVL(u.name, 'SYS'), o.name, o.subname,
       so.object_type, s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(s.groups, 0, 1, s.groups)),
       s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
     sys.file$ f
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = u.user# (+)
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
  and s.ts# = f.ts#
  and s.file# = f.relfile#
union all
select NVL(u.name, 'SYS'), un.name, NULL,
       decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'), s.type#,
       ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
       s.maxexts, s.extpct,
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.groups, 0, 1, s.groups)),
       s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
  and s.block# = un.block#
  and s.ts# = un.ts#
  and s.ts# = ts.ts#
  and s.user# = u.user# (+)
  and s.type# in (1, 10)
  and un.status$ != 1
  and un.ts# = f.ts#
  and un.file# = f.relfile#
union all
select NVL(u.name, 'SYS'), to_char(f.file#) || '.' || to_char(s.block#), NULL,
       decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',
                      4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.groups, 0, 1, s.groups)),
       s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
  and s.user# = u.user# (+)
  and s.type# not in (1, 5, 6, 8, 10)
  and s.ts# = f.ts#
  and s.file# = f.relfile#；


其中和rollback有关的一段是：
select NVL(u.name, 'SYS'), un.name, NULL,
       decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'), s.type#,
       ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
       s.maxexts, s.extpct,
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(s.groups, 0, 1, s.groups)),
       s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
  and s.block# = un.block#
  and s.ts# = un.ts#
  and s.ts# = ts.ts#
  and s.user# = u.user# (+)
  and s.type# in (1, 10)
  and un.status$ != 1
  and un.ts# = f.ts#
  and un.file# = f.relfile#;

发现这里的decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO')函数中有两部分的rollback，即rollback和type2 undo,我们来看一下segment的type：
SQL> select distinct segment_type from dba_segments;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE SUBPARTITION
NESTED TABLE
TABLE PARTITION
ROLLBACK
LOB PARTITION
LOBSEGMENT
TABLE
INDEX
CLUSTER

SEGMENT_TYPE
------------------
TYPE2 UNDO

12 rows selected.

SQL>

发现还有一个type2 undo的segment type, 因此得出结论，我们的开始查询到的结果是正确的，优化一下我们的查询就可以发现结果应该是一致的。
SQL>select segment_name,tablespace_name from dba_segments where segment_type in ('ROLLBACK','TYPE2 UNDO');
SQL> select segment_name,tablespace_name from dba_segments where segment_type in ('ROLLBACK','TYPE2 UNDO');

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
SYSTEM               SYSTEM
_SYSSMU1_2986795754$ UNDOTBS1
_SYSSMU2_346913448$  UNDOTBS1
_SYSSMU3_219912871$  UNDOTBS1
_SYSSMU4_2796668278$ UNDOTBS1
_SYSSMU5_1189263717$ UNDOTBS1
_SYSSMU6_2463593703$ UNDOTBS1
_SYSSMU7_2494227896$ UNDOTBS1
_SYSSMU8_625026625$  UNDOTBS1
_SYSSMU9_3163463872$ UNDOTBS1
_SYSSMU10_84651485$  UNDOTBS1

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
_SYSSMU11_2341436089 UNDOTBS2
$

_SYSSMU12_3635810052 UNDOTBS2
$

_SYSSMU13_1220581021 UNDOTBS2
$

_SYSSMU14_2107515918 UNDOTBS2
$

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------

_SYSSMU15_1935625349 UNDOTBS2
$

_SYSSMU16_3317338710 UNDOTBS2
$

_SYSSMU17_3412184530 UNDOTBS2
$

_SYSSMU18_1923051430 UNDOTBS2

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
$

_SYSSMU19_4280175001 UNDOTBS2
$

_SYSSMU20_1492722088 UNDOTBS2
$


21 rows selected.

结果一致。