深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)
深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)
今天在查看系统空间使用情况的时候，发现一个细节的问题，自己死磕了一把，还是发现了不少有价值的东西。
事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间的时候，如果发现有些临时用户占用的空间过大，就需要协调开发去做一些清理，但是这次用户占用的空间表空间使用情况有很大的差异。
查看用户占用空间的情况如下，可以看到总体用户占用的空间在2T多一些。
USERNAME Default TBS TEMP TBS CREATED Size (Mb)
------------------------------ --------------- --------------- --------- ------------
PRDAPPO DATAS01 TEMP 12-JAN-13 2,531,124
SYS SYSTEM TEMP 26-DEC-12 21,018
...
------------
sum 2,670,364
但是查看表空间的使用情况时，发现表空间的使用情况如下，总共占用了近6T的数据，使用了大概有5T的样子，那么5-2.6=2.4T,剩下的近2T的空间哪去了？怎么没有统计出来呢？
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
-------------------- --- - - ---- ------------ ---------- ----------- -------- ------------ --- -
------------ ---------- -----------
sum 6,188,911 928,277 5,260,634带着这个疑问开始了详细的排查。
首先使用dba_segments查看了占用做多空间的用户。发现占用空间时4.7T左右。
SQL> select sum(bytes)/1024/1024 size_MB from dba_segments where owner='PRDAPPO';
SIZE_MB
----------
4745321.13
那使用shell脚本查看数据库用户占用情况时引用的数据库视图是哪个呢？
先贴出脚本的大体内容来。
select
USERNAME,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
CREATED,
nvl(sum(seg.blocks*ts.blocksize)/1024/1024,0) MB
from
sys.ts$ ts,
sys.seg$ seg,
sys.user$ us,
dba_users du
where
us.name (+)= du.username
and seg.user# (+)= us.user#
and ts.ts# (+)= seg.ts#
group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED
order by MB desc,username,created
标黄的部分就是计算占用空间大小的。可以看到直接是从sys.seg$里面去取的blocks
为了更加清晰的复现问题，我们再来试一遍。
首先查到username对应的user id作为seg$中使用。
select *from all_users where username='PRDAPPO';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
PRDAPPO 48 12-JAN-13
sys.seg$的表结构如下啊：
SQL> desc sys.seg$
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
TYPE# NOT NULL NUMBER
TS# NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
EXTENTS NOT NULL NUMBER
INIEXTS NOT NULL NUMBER
MINEXTS NOT NULL NUMBER
MAXEXTS NOT NULL NUMBER
EXTSIZE NOT NULL NUMBER
EXTPCT NOT NULL NUMBER
USER# NOT NULL NUMBER
LISTS NUMBER
GROUPS NUMBER
BITMAPRANGES NOT NULL NUMBER
CACHEHINT NOT NULL NUMBER
SCANHINT NOT NULL NUMBER
HWMINCR NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SQL> select sum(blocks) from sys.seg$ where user#=48;
SUM(BLOCKS)
-----------
323983920
可以看到通过sys.seg$去查找userid对应的空间占用情况，是2.53T。
SQL> select 323983920*1024*8/1024/1024 size_MB from dual;
SIZE_MB
----------
2531124.38
这个情况和使用dba_segments相比直接少了2.2T左右，如果差得小，可能也还能解释得通，差得实在太多了。
sys.seg$算是这些数据字典表dba_segments的基表，里面的信息应该是很准备很完整。带着疑问我们来看看dba_segments的定义是什么样的。
一通调用分析，找到同义词，找到视图，最后找到定义的内容，dba_segments是基于sys_dba_segs这个视图是在sys下的。
> ksh showdict.sh dba_segments
object_details
OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
------------------------------ ---------- -------------- ------------------------------ -------------------
SYS 4099 DBA_SEGMENTS VIEW
PUBLIC 4100 DBA_SEGMENTS SYNONYM
synonym_details
OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC DBA_SEGMENTS
view_details
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
DBA_SEGMENTS select owner, segment_name, partition_name, segment_type,
segment_subtype, 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, max_size,
retention, minretention,
pct_increase, freelists, freelist_groups, relative_fno,
decode(buffer_pool_id, 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
decode(flash_cache, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
decode(cell_flash_cache, 1, 'KEEP', 2, 'NONE', 'DEFAULT')
from sys_dba_segs
直接看sys_dba_segs可能感觉还得不到任何信息，我们来看看sys_dba_segs更进一步的信息，可以看到啊还是基于sys.seg$，但是分成了3部分，最后做了union all
> ksh showdict.sh sys_dba_segs
object_details
OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
------------------------------ ---------- -------------- ------------------------------ -------------------
SYS 4096 SYS_DBA_SEGS VIEW
synonym_details
no rows selected
view_details
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
SYS_DBA_SEGS select NVL(u.name, 'SYS'), o.name, o.subname,
so.object_type, s.type#,
decode(bitand(s.spare1, 2097408), 2097152, 'SECUREFILE', 256, 'ASSM', 'MSSM'),
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,
decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),
to_char(decode(bitand(s.spare1, 2097152), 2097152,
decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX',
4, 'DEFAULT', 'INVALID'), NULL)),
decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL),
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#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,
bitand(s.cachehint, 48)/16, 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#,
NULL, 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,
decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),
NULL, 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#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,
bitand(s.cachehint, 48)/16, 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#,
NULL, 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,
decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),
NULL, NULL, 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#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,
bitand(s.cachehint, 48)/16, 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#
问题到了这感觉应该有头绪了，但是其实问题的分析才刚刚开始，明天再更新下半段内容。