今天在檢視系統空間使用情況的時候，發現一個細節的問題，自己死磕了一把，還是發現了不少有價值的東西。
事情的起因是我在使用指令碼在某個環境中檢視每個使用者所佔有的空間的時候，如果發現有些臨時使用者佔用的空間過大，就需要協調開發去做一些清理，但是這次使用者佔用的空間表空間使用情況有很大的差異。
檢視使用者佔用空間的情況如下，可以看到總體使用者佔用的空間在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#

問題到了這感覺應該有頭緒了，但是其實問題的分析才剛剛開始，明天再更新下半段內容。

深度解析dba_segments和sys.seg$中的細節差異(上)

相關文章