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

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