深度解析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# 问题到了这感觉应该有头绪了，但是其实问题的分析才刚刚开始，明天再更新下半段内容。