Oracle 计算带blob表的大小

作为运维DBA,导数据是我们的一个非常基础的工作,导数据之前,要估算一个表的大小,磁盘剩余空间等等,这个工作才能开展下去。如果导普通的表(不带blob),则可能非常简单,有时候只需要统计一下dba_segments就行,但是带blob字段的表则就不能直接统计该视图,Oracle中 lob字段采用独立的lob segment来存储,因此表的大小不能只查看dba_segments,还需要看dba_lobs,以下就是该方法


通用方法:

SELECT  
 (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)     -- The Table Segment size  
  FROM DBA_SEGMENTS S  
  WHERE S.OWNER = UPPER('&SCHEMA') 
  AND  S.SEGMENT_NAME = UPPER('&TABNAME')) +  
  (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)   -- The Lob Segment Size   
  FROM DBA_SEGMENTS S, DBA_LOBS L  
  WHERE S.OWNER = L.OWNER 
  AND L.SEGMENT_NAME = S.SEGMENT_NAME 
  AND L.TABLE_NAME = UPPER('&TABNAME') 
  AND L.OWNER = UPPER('&SCHEMA')) +  
 (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Lob Index size  
  FROM DBA_SEGMENTS S, DBA_INDEXES I  
  WHERE S.OWNER = I.OWNER 
  AND  I.INDEX_NAME = S.SEGMENT_NAME 
  AND I.TABLE_NAME = UPPER('&TABNAME') 
  AND INDEX_TYPE = 'LOB' 
  AND I.OWNER = UPPER('&SCHEMA')) +
  (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Index size  
  FROM DBA_SEGMENTS S, DBA_INDEXES I  
  WHERE S.OWNER = I.OWNER 
  AND  I.INDEX_NAME = S.SEGMENT_NAME 
  AND I.TABLE_NAME = UPPER('&TABNAME') 
  AND INDEX_TYPE = 'NORMAL' 
  AND I.OWNER = UPPER('&SCHEMA'))
   AS "TOTAL TABLE SIZE"  
FROM DUAL;  
测试:
ZXY@ zxy>SELECT  
  2   (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)     -- The Table Segment size  
  3    FROM DBA_SEGMENTS S  
  4    WHERE S.OWNER = UPPER('zxy') 
  5    AND  S.SEGMENT_NAME = UPPER('t_sfzslxx_1100')) +  
  6    (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)   -- The Lob Segment Size   
  7    FROM DBA_SEGMENTS S, DBA_LOBS L  
  8    WHERE S.OWNER = L.OWNER 
  9    AND L.SEGMENT_NAME = S.SEGMENT_NAME 
 10    AND L.TABLE_NAME = UPPER('t_sfzslxx_1100') 
 11    AND L.OWNER = UPPER('zxy')) +  
 (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Lob Index size  
 13    FROM DBA_SEGMENTS S, DBA_INDEXES I  
 14    WHERE S.OWNER = I.OWNER 
 15    AND  I.INDEX_NAME = S.SEGMENT_NAME 
 16    AND I.TABLE_NAME = UPPER('t_sfzslxx_1100') 
 17    AND INDEX_TYPE = 'LOB' 
 18    AND I.OWNER = UPPER('zxy')) +
 19    (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Index size  
 20    FROM DBA_SEGMENTS S, DBA_INDEXES I  
 21    WHERE S.OWNER = I.OWNER 
 22    AND  I.INDEX_NAME = S.SEGMENT_NAME 
 23    AND I.TABLE_NAME = UPPER('t_sfzslxx_1100') 
 24    AND INDEX_TYPE = 'NORMAL' 
 25    AND I.OWNER = UPPER('zxy'))
 26     AS "TOTAL TABLE SIZE"  
 27  FROM DUAL;  

TOTAL TABLE SIZE
----------------
           26.37



阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页