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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-05-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

insert中启用错误日志的问题及分析(r2第10天)

在平时的工作中,有时候需要insert一批数据,这些数据可能是临时表,外部表,普通表,子查询等形式,类似下面的格式 insert into xxxx (sele...

3049
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

3558
来自专栏乐沙弥的世界

RAC环境下的阻塞(blocking blocked)

      RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为...

1202
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(上)

经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

2133
来自专栏乐沙弥的世界

Oracle 角色、配置文件

增加或删除角色中的某一权限,被授予该角色的所有用户或角色自动地获得新增权限或删除旧的权限

892
来自专栏乐沙弥的世界

Oracle 聚簇因子(Clustering factor)

    聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一,决定了当前的SQL语句是否走索引,还是全表扫描以及是否作为嵌套连接外...

1471
来自专栏杨建荣的学习笔记

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

3086
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3723
来自专栏数据和云

触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

1414
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

39413

扫码关注云+社区

领取腾讯云代金券