查询Oracle 数据库中带有lob字段的某一个表的大小，oraclelob
注意：由于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('ZWPZK'))) +
(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('ZWPZK') 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('ZWPZK') 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;
35718692864/1024/1024/1024
--------------------------
3.3265625
写了一个，你试试：
select a.TABLESPACE_NAME "TableSpace Name",
round(a.BYTES / 1024 / 1024) "MB Allocated",
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used",
nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free",
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) "Pct Used"
from (select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
And a.tablespace_name in ('表空间名','表空间名','表空间名');
--我这里说的字段名跟列 是同一个意思select column_name,table_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where column_name='字段名'; --根据字段名查出相关的表名出来。记录下来--然后对查出来的表进行查询，找到含这内容字段的表select * from 表名 where 字段名='xiaoming'