在做一个测试的时候发现从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.
结果一致。
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.
结果一致。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7734298/viewspace-706681/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7734298/viewspace-706681/