Oracle数据库对象大小计算及删除记录后真实表大小测算

一、不含空闲空间的表、索引、分区及LOB段大小计算 #

要排除空闲空间计算对象真实占用的空间，不能依赖DBA_SEGMENTS（该视图返回段的总大小，包含未被使用的空闲块），需使用Oracle记录实际使用空间的系统视图，以下是各类对象的查询方式：

1. 普通表 #

SELECT 
  owner,
  table_name,
  TRUNC((used_space * block_size)/1024/1024) AS used_size_mb
FROM dba_tables t
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name;

说明：used_space为表实际使用的数据块数，乘以表空间的block_size得到实际占用字节数，转换为MB单位。

2. 表分区 #

SELECT 
  owner,
  table_name,
  partition_name,
  TRUNC((used_space * block_size)/1024/1024) AS used_size_mb
FROM dba_tab_partitions tp
JOIN dba_tablespaces ts ON tp.tablespace_name = ts.tablespace_name;

3. 索引 #

SELECT 
  owner,
  index_name,
  table_name,
  TRUNC((used_space * block_size)/1024/1024) AS used_size_mb
FROM dba_indexes i
JOIN dba_tablespaces ts ON i.tablespace_name = ts.tablespace_name;

4. LOB段与LOB分区 #

-- LOB段查询
SELECT 
  owner,
  table_name,
  column_name,
  segment_name,
  TRUNC((used_space * block_size)/1024/1024) AS used_size_mb
FROM dba_lobs l
JOIN dba_tablespaces ts ON l.tablespace_name = ts.tablespace_name;

-- LOB分区查询
SELECT 
  owner,
  table_name,
  partition_name,
  column_name,
  segment_name,
  TRUNC((used_space * block_size)/1024/1024) AS used_size_mb
FROM dba_lob_partitions lp
JOIN dba_tablespaces ts ON lp.tablespace_name = ts.tablespace_name;

二、删除数据后未Shrink的大表真实大小计算 #

你使用DBA_SEGMENTS的查询返回的是段的总大小，包含删除数据后遗留的空闲块（删除操作不会降低表的高水位线HWM），要得到真实占用空间，可通过以下两种方式：

方法1：利用DBA_TABLES视图（11g及以上版本适用） #

SELECT 
  owner,
  table_name,
  TRUNC((used_space * (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024) AS actual_used_mb,
  TRUNC((num_rows * avg_row_len)/1024/1024) AS estimated_mb -- 基于行平均长度的估算值，供交叉验证
FROM dba_tables
WHERE owner = 'YOUR_SCHEMA' 
  AND table_name = 'YOUR_TABLE';

说明：used_space直接记录表实际使用的数据块数，乘以数据库块大小得到真实占用空间；estimated_mb通过总行数乘以平均行长度计算，可辅助验证结果准确性。

方法2：使用DBMS_SPACE存储过程查询 #

DECLARE
  l_free_blocks NUMBER;
  l_total_blocks NUMBER;
  l_used_blocks NUMBER;
  l_block_size NUMBER;
BEGIN
  -- 获取表所在表空间的块大小
  SELECT block_size 
  INTO l_block_size 
  FROM dba_tablespaces 
  WHERE tablespace_name = (SELECT tablespace_name FROM dba_tables WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE');
  
  -- 查询空间使用情况
  DBMS_SPACE.SPACE_USAGE(
    segment_owner => 'YOUR_SCHEMA',
    segment_name => 'YOUR_TABLE',
    segment_type => 'TABLE',
    total_blocks => l_total_blocks,
    used_blocks => l_used_blocks,
    free_blocks => l_free_blocks
  );
  
  -- 输出结果
  DBMS_OUTPUT.PUT_LINE('实际使用大小(MB): ' || TRUNC((l_used_blocks * l_block_size)/1024/1024));
  DBMS_OUTPUT.PUT_LINE('总段大小(MB): ' || TRUNC((l_total_blocks * l_block_size)/1024/1024));
  DBMS_OUTPUT.PUT_LINE('空闲空间大小(MB): ' || TRUNC((l_free_blocks * l_block_size)/1024/1024));
END;
/

说明：该存储过程直接返回段的总块数、已使用块数和空闲块数，计算后可得到真实占用空间，同时能直观看到未释放的空闲空间大小。

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