Oracle 计算带blob表的大小

最新推荐文章于 2024-03-19 17:24:19 发布

Lightning_2017

最新推荐文章于 2024-03-19 17:24:19 发布

阅读量1.9k

点赞数

本文链接：https://blog.csdn.net/qq_31943653/article/details/79976503

版权

作为运维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