查询Oracle 数据库中带有lob字段的表的大小

注意：由于lob字段有独立的lob segment来存储，故对于带有lob字段的表，不能仅仅查询dba_segments. 
以下脚本来自：
How to Compute the Size of a Table containing Outline CLOBs and BLOBs[Article ID 118531.1]
经过修改:改为了NVL(SUM(S.BYTES),0) 

 

SQL> col "TOTAL TABLE SIZE" format 99999999999999;
---注意：以下sql可以直接执行。为了方便复制该脚本，因此不贴SQL语句的log，而是放上了执行时的SQL语句。
SELECT
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Table Segment size
   FROM DBA_SEGMENTS S
   WHERE S.OWNER = UPPER('LC0019999') AND
           (S.SEGMENT_NAME = UPPER('ZWPZGS'))) +
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Lob Segment Size
   FROM DBA_SEGMENTS S, DBA_LOBS L
   WHERE S.OWNER = UPPER('LC0019999') AND
           (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ZWPZGS') AND L.OWNER = UPPER('LC0019999'))) +
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Lob Index size
   FROM DBA_SEGMENTS S, DBA_INDEXES I
   WHERE S.OWNER = UPPER('LC0019999') AND
           (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ZWPZGS') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LC0019999')))
   "TOTAL TABLE SIZE"
FROM DUAL;


TOTAL TABLE SIZE
----------------
     3571869286

SQL> select 3571869286/1024/1024/1024 from dual;
3571869286/1024/1024/1024
--------------------------
                 3.3265625

 

SQL> col "TOTAL TABLE SIZE" format 99999999999999;
---注意：以下sql可以直接执行。为了方便复制该脚本，因此不贴SQL语句的log，而是放上了执行时的SQL语句。
SELECT
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Table Segment size
   FROM DBA_SEGMENTS S
   WHERE S.OWNER = UPPER('LC0019999') AND
           (S.SEGMENT_NAME = UPPER('ZWPZGS'))) +
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Lob Segment Size
   FROM DBA_SEGMENTS S, DBA_LOBS L
   WHERE S.OWNER = UPPER('LC0019999') AND
           (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ZWPZGS') AND L.OWNER = UPPER('LC0019999'))) +
  (SELECT NVL(SUM(S.BYTES),0)                                                  -- The Lob Index size
   FROM DBA_SEGMENTS S, DBA_INDEXES I
   WHERE S.OWNER = UPPER('LC0019999') AND
           (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ZWPZGS') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LC0019999')))
   "TOTAL TABLE SIZE"
FROM DUAL;

TOTAL TABLE SIZE
----------------
     3571869286

SQL> select 3571869286/1024/1024/1024 from dual;
3571869286/1024/1024/1024
--------------------------
                 3.3265625

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29345367/viewspace-1816217/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29345367/viewspace-1816217/