Oracle 19c中LOB迁移占用空间超实际值，如何查询准确迁移大小？

通过以下SQL查询得到LOB对象大小为100GB：

SELECT BYTES/ (1024*1024*1024) GB
 FROM DBA_SEGMENTS D
 JOIN DBA_LOBS L ON L.SEGMENT_NAME = D.SEGMENT_NAME
WHERE D.OWNER = 'SCHEMA_NAME' AND SEGMENT_NAME = 'MY_LOB'

查询结果：

但将该LOB对象迁移至其他表空间时，实际占用空间超过100GB。Oracle版本为19c，如何查询LOB对象的准确迁移大小？

要获取LOB迁移时的准确占用空间，需考虑LOB段的实际使用空间、存储参数开销等因素，可通过以下方式查询：

1. 查询LOB段实际使用与已分配空间 #

该方法能区分LOB实际用了多少空间，以及已分配的总空间（含未使用块）：

SELECT 
  l.table_name,
  l.column_name,
  (s.used_blocks * t.block_size)/1024/1024/1024 AS used_gb,
  (s.allocated_blocks * t.block_size)/1024/1024/1024 AS allocated_gb
FROM 
  dba_lobs l
JOIN 
  dba_tablespaces t ON l.tablespace_name = t.tablespace_name
JOIN 
  dba_segments s ON s.segment_name = l.segment_name 
                  AND s.owner = l.owner
WHERE 
  l.owner = 'SCHEMA_NAME' 
  AND l.segment_name = 'MY_LOB';

迁移时实际占用空间更接近used_gb，但需结合存储参数判断额外开销。

2. 检查LOB存储参数 #

LOB的存储参数会直接影响迁移后的空间占用，可通过以下SQL查看：

SELECT 
  owner,
  segment_name,
  chunk,
  pctversion,
  cache,
  tablespace_name
FROM 
  dba_lobs
WHERE 
  owner = 'SCHEMA_NAME' 
  AND segment_name = 'MY_LOB';

• CHUNK：LOB块的最小分配单位，即使单条LOB数据很小，也会占用至少一个CHUNK空间
• PCTVERSION：保留旧版本LOB数据的比例，迁移时可能额外占用空间
• CACHE：是否缓存LOB数据，会影响存储块的分配方式

3. 用存储过程估算迁移空间 #

如果通过ALTER TABLE ... MOVE LOB ...迁移，可使用DBMS_SPACE.CREATE_TABLE_COST估算目标空间需求：

DECLARE
  l_used_bytes NUMBER;
  l_alloc_bytes NUMBER;
BEGIN
  DBMS_SPACE.CREATE_TABLE_COST(
    tablespace_name => 'TARGET_TABLESPACE',
    table_name => 'TABLE_NAME',
    lob_columns => 'LOB_COLUMN_NAME',
    used_bytes => l_used_bytes,
    alloc_bytes => l_alloc_bytes
  );
  DBMS_OUTPUT.PUT_LINE('估算已使用空间(GB): ' || l_used_bytes/1024/1024/1024);
  DBMS_OUTPUT.PUT_LINE('估算分配空间(GB): ' || l_alloc_bytes/1024/1024/1024);
END;
/

需替换TARGET_TABLESPACE、TABLE_NAME、LOB_COLUMN_NAME为实际值。

4. 查看LOB碎片率 #

LOB段的碎片会影响迁移后的空间占用，可查询碎片率判断整理后的空间变化：

SELECT 
  owner,
  segment_name,
  ROUND((1 - (s.used_blocks / s.allocated_blocks))*100, 2) AS fragmentation_pct
FROM 
  dba_segments s
JOIN 
  dba_lobs l ON s.segment_name = l.segment_name 
             AND s.owner = l.owner
WHERE 
  l.owner = 'SCHEMA_NAME' 
  AND l.segment_name = 'MY_LOB';

碎片率高的LOB迁移后，空间占用会更接近实际使用大小，但目标表空间的存储参数仍可能导致差异。

内容的提问来源于stack exchange，提问作者AleksRous