oracle 怎样查询含blob字段的表所占空间的大小
oracle 怎样查询含blob字段的表TEST所占空间的大小，我自己用以下语句测试不知道是否正确，请刘大指点，谢谢select a.table_name,a.segment_name,b.tablespace_name, b.bytes --b.bytes 为lob字段总大小？
from user_lobs a, user_segments b where a.segment_name=b.segment_name and a.TABLE_NAME='TEST' 请参考以下脚本：col segment_name format a30
set pagesize 10000
select a.owner, a.segment_name, a.segment_type,
sum(a.bytes)/1024/1024 Bytes_MB
from dba_segments a, all_lobs b
where a.segment_name = b.segment_name
group by a.owner, a.segment_name, a.segment_type; 好的，谢谢 刘大。。。 How to Compute the Size of a Table containing Outline CLOBs and BLOBs (文档 ID 118531.1)
参考这个 在DBA_SEGMENTS的BYTES列中显示的表大小并不包括LOB(CLOB或BLOB)段大小。
因为为了对那些包含有LOB的表进行总体大小计算，我们就必须考虑：
表的字节大小 => 值取自dba_segments
+
LOB段的字节大小 => 值取自dba_lobs和dba_segments，其segment_type为LOBSEGMENT
+
LOB索引的字节大小 => 值取自dba_indexes和dba_segments, 其segment_type为LOBINDEXACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name: '
SELECT
(SELECT SUM(S.BYTES) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('&SCHEMA') AND
(S.SEGMENT_NAME = UPPER('&TABNAME'))) +
(SELECT SUM(S.BYTES) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('&SCHEMA') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
(SELECT SUM(S.BYTES) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('&SCHEMA') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
"TOTAL TABLE SIZE"
FROM DUAL;
页:
[1]