查询Oracle中带有Clob字段的表的大小

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/m0_37941193/article/details/79979972

在oracle里面,由于lob字段有独立的lob segment来存储,所以查询需要DBA_SEGMENTS S, DBA_INDEXES  2张表结合才可查出结果。

以下是实验SQL说明:

表名:GJ_NORM_POOL

表归属用户名:ORACLE

SELECT  
 (SELECT NVL(SUM(S.BYTES),0)                                                                                               -- The Table Segment size  
  FROM DBA_SEGMENTS S  
  WHERE S.OWNER = UPPER('ORACLE') AND  
       (S.SEGMENT_NAME = UPPER('GJ_NORM_POOL'))) +  
 (SELECT NVL(SUM(S.BYTES),0)                                                                                               -- The Lob Segment Size  
  FROM DBA_SEGMENTS S, DBA_LOBS L  
  WHERE S.OWNER = UPPER('ORACLE') AND  
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('GJ_NORM_POOL') AND L.OWNER = UPPER('ORACLE'))) +  
 (SELECT NVL(SUM(S.BYTES),0)                                                                                               -- The Lob Index size  
  FROM DBA_SEGMENTS S, DBA_INDEXES I  
  WHERE S.OWNER = UPPER('ORACLE') AND  
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('GJ_NORM_POOL') AND INDEX_TYPE = 'CLOB' AND I.OWNER = UPPER('ORACLE')))  
  "TOTAL TABLE SIZE"  
FROM DUAL;  
TOTAL TABLE SIZE
13500416

select 13500416/1024/1024/1024 from dual;  
0.0125732421875
总结:oracle用户下的拥有CLOB字段的表GJ_NORM_POOL总大小为0.0125732421875G.

阅读更多
想对作者说点什么? 我来说一句

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