综合统计 Oracle 表行数、表大小、索引大小及 LOB 大小的 SQL

--综合统计 Oracle 表行数、表大小、索引大小及 LOB 大小的 SQL 1
SELECT
dt.owner,
dt.table_name,
dt.num_rows AS 行数,
ROUND(SUM(CASE WHEN ds.segment_type = 'TABLE' THEN ds.bytes ELSE 0 END)/1024/1024, 2) AS 表大小_MB,
ROUND(SUM(CASE WHEN ds.segment_type = 'INDEX' THEN ds.bytes ELSE 0 END)/1024/1024, 2) AS 索引大小_MB,
ROUND(SUM(CASE WHEN ds.segment_type = 'LOBSEGMENT' THEN ds.bytes ELSE 0 END)/1024/1024, 2) AS LOB大小_MB
FROM
dba_tables dt
LEFT JOIN dba_segments ds ON dt.owner = ds.owner AND (ds.segment_name = dt.table_name OR ds.segment_name IN (SELECT index_name FROM dba_indexes WHERE table_owner = dt.owner AND table_name = dt.table_name) OR ds.segment_name IN (SELECT segment_name FROM dba_lobs WHERE owner = dt.owner AND table_name = dt.table_name))
GROUP BY dt.owner, dt.table_name, dt.num_rows;

不过这样可能效率不高，因为JOIN条件复杂。可能需要分开处理表、索引和LOB，然后合并结果。

另一种方法是分别统计表、索引和LOB的大小，然后通过子查询或CTE将它们合并。例如：

--综合统计 Oracle 表行数、表大小、索引大小及 LOB 大小的 SQL 2
WITH
table_size AS (
SELECT owner, segment_name AS table_name, SUM(bytes) AS bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
),
index_size AS (
SELECT i.table_owner AS owner, i.table_name, SUM(s.bytes) AS bytes
FROM dba_indexes i
JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
WHERE s.segment_type = 'INDEX'
GROUP BY i.table_owner, i.table_name
),
lob_size AS (
SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes
FROM dba_lobs l
JOIN dba_segments s ON l.owner = s.owner AND l.segment_name = s.segment_name
GROUP BY l.owner, l.table_name
)
SELECT
dt.owner,
dt.table_name,
dt.num_rows AS 行数,
ROUND(COALESCE(ts.bytes, 0)/1024/1024, 2) AS 表大小_MB,
ROUND(COALESCE(isz.bytes, 0)/1024/1024, 2) AS 索引大小_MB,
ROUND(COALESCE(ls.bytes, 0)/1024/1024, 2) AS LOB大小_MB
FROM
dba_tables dt
LEFT JOIN table_size ts ON dt.owner = ts.owner AND dt.table_name = ts.table_name
LEFT JOIN index_size isz ON dt.owner = isz.owner AND dt.table_name = isz.table_name
LEFT JOIN lob_size ls ON dt.owner = ls.owner AND dt.table_name = ls.table_name
WHERE
dt.owner = '用户名称' AND dt.table_name = '表名';

这种方法可能更清晰，且效率更高。同时，每个部分单独处理，便于理解和维护。
需要验证各部分的正确性。表大小直接从dba_segments中segment_type='TABLE'获取。索引大小需要关联dba_indexes和dba_segments，确保索引属于该表。LOB大小则通过dba_lobs找到对应的segment_name，再连接dba_segments。
此外，num_rows需要统计信息准确，可能需要提醒用户先执行统计信息收集，或者在查询中包含提示。
最后，权限方面，确保用户有访问dba_tables、dba_segments、dba_indexes和dba_lobs的权限，否则替换为user_*视图。
综上，最终的SQL可能采用CTE方式，结构清晰，各部分分别统计后合并，确保正确获取表、索引和LOB的大小。

-- 执行前确保统计信息更新（可选）  
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '用户名称', tabname => '表名', cascade => TRUE);  

--综合统计 Oracle 表行数、表大小、索引大小及 LOB 大小的 SQL 3
SELECT  
    dt.owner AS 用户,  
    dt.table_name AS 表名,  
    dt.num_rows AS 行数,  
    ROUND(NVL(ts.table_bytes, 0)/1024/1024, 2) AS 表大小_MB,  
    ROUND(NVL(idx.index_bytes, 0)/1024/1024, 2) AS 索引大小_MB,  
    ROUND(NVL(lob.lob_bytes, 0)/1024/1024, 2) AS LOB大小_MB  
FROM  
    dba_tables dt  
LEFT JOIN (  
    SELECT owner, segment_name, SUM(bytes) AS table_bytes  
    FROM dba_segments  
    WHERE segment_type = 'TABLE'  
    GROUP BY owner, segment_name  
) ts  
    ON dt.owner = ts.owner AND dt.table_name = ts.segment_name  
LEFT JOIN (  
    SELECT table_owner, table_name, SUM(bytes) AS index_bytes  
    FROM dba_indexes i  
    JOIN dba_segments s  
        ON i.owner = s.owner AND i.index_name = s.segment_name  
    WHERE s.segment_type = 'INDEX'  
    GROUP BY table_owner, table_name  
) idx  
    ON dt.owner = idx.table_owner AND dt.table_name = idx.table_name  
LEFT JOIN (  
    SELECT l.owner, l.table_name, SUM(s.bytes) AS lob_bytes  
    FROM dba_lobs l  
    JOIN dba_segments s  
        ON l.owner = s.owner AND l.segment_name = s.segment_name  
    GROUP BY l.owner, l.table_name  
) lob  
    ON dt.owner = lob.owner AND dt.table_name = lob.table_name  
WHERE  
    dt.owner = '用户名称'  
    AND dt.table_name = '表名';  

关键逻辑说明
‌行数统计‌：
从 dba_tables.num_rows 获取表行数，依赖统计信息准确性‌14。
‌表大小‌：
通过 dba_segments 中 segment_type='TABLE' 的 bytes 字段统计物理存储空间‌。
‌索引大小‌：
关联 dba_indexes 和 dba_segments，筛选 segment_type='INDEX' 的索引段统计‌。
‌LOB 大小‌：
通过 dba_lobs 关联 dba_segments，获取 LOB 字段的独立存储段大小‌。
注意事项
‌权限要求‌：需有 SELECT 权限访问 dba_tables、dba_segments、dba_indexes 和 dba_lobs；普通用户可替换为 user_* 视图‌。
‌统计信息更新‌：若 num_rows 为空或不准确，需执行 DBMS_STATS.GATHER_TABLE_STATS‌14。
‌结果差异‌：
物理存储大小（dba_segments.bytes）包含未使用的预分配块，可能大于实际数据占用‌23。
索引和 LOB 字段的大小仅统计与当前表关联的对象‌12。
扩展场景
‌批量统计用户下所有表‌：移除 WHERE 条件并添加 ORDER BY 排序‌。
‌LOB 字段详情‌：可单独查询 dba_lobs 和 dba_segments 获取字段级存储分布‌。