Oracle动态查询：批量统计2000+表年度行数与存储大小方案问询

针对你要对2000+张带CREATE_DATE字段的表，批量关联系统表的avg_row_len值、按年份分组统计行数和存储大小的需求，完全不需要逐张手动执行单表查询，有两种可直接落地的实现方式：

方案1：动态SQL批量拼接（无特殊权限即可用） #

这个方式不需要创建自定义函数，只要有查询系统视图的权限就能用，核心是提前把所有表的元数据（表名、所属schema、avg_row_len值）关联好，再把每张表的统计SQL用UNION ALL拼成一个完整的大查询，一次执行拿到全部结果。

• 第一步：先筛选符合要求的目标表清单，必须提前过滤掉没有CREATE_DATE字段的表，避免动态SQL执行时报错。用如下查询拉取元数据：

-- 拉取所有带CREATE_DATE字段的目标表元数据，可根据自身需求加schema、表名前缀过滤条件
WITH target_tables AS (
    SELECT 
        t.owner,
        t.table_name,
        t.avg_row_len
    FROM all_tables t
    -- 关联校验表是否存在CREATE_DATE字段
    INNER JOIN all_tab_columns c
        ON t.owner = c.owner 
        AND t.table_name = c.table_name
        AND c.column_name = 'CREATE_DATE'
    -- 按需替换成你实际要统计的schema范围
    WHERE t.owner = '你的目标Schema名'
)

• 第二步：自动拼接全量可执行的统计SQL，拼接过程会把提前查到的avg_row_len直接嵌入到每张表的统计逻辑里，不用手动传参：

-- 生成完整的批量统计SQL语句
SELECT LISTAGG(
    'SELECT 
        '''||owner||''' as table_owner,
        '''||table_name||''' as table_name,
        '||avg_row_len||' as avg_row_len,
        COUNT(1) as "Number of Count",
        ROUND(COUNT(1) * '||avg_row_len||'/1024/1024) AS "Size in MB",
        TO_CHAR(CREATE_DATE, ''YYYY'') AS YEAR,
        ''CREATE_DATE'' AS DATTT
    FROM "'||owner||'"."'||table_name||'"
    GROUP BY TO_CHAR(CREATE_DATE, ''YYYY'')',
    ' UNION ALL '
) WITHIN GROUP (ORDER BY table_name) AS full_batch_sql
FROM target_tables;

• 第三步：把上一步输出的full_batch_sql结果复制出来直接执行，就能一次性返回所有目标表的全维度统计结果。

这个方案的常见适配场景：如果2000多张表拼接出来的SQL长度超过Oracle字符串长度限制，可以把表清单按每500张分一组，分别拼接执行后合并结果即可；拼接时一定要用双引号包裹schema和表名，避免特殊命名的表执行报错。

方案2：自定义管道函数（适合长期重复统计的场景） #

如果后续需要定期跑这类统计，可以申请权限创建一个管道函数，实现一键执行全量统计，不用每次手动拼SQL：

• 先定义匹配输出结构的对象类型，包含表所有者、表名、总行数、存储大小、统计年份、日期字段标识这些字段。
• 在函数内部遍历之前筛好的目标表清单，对每张表通过EXECUTE IMMEDIATE执行对应的分组统计逻辑，把计算结果通过管道返回。
• 给函数加异常捕获逻辑：遇到没有权限访问、字段类型不兼容的表，直接跳过记录日志，不会因为单张表报错导致整个批量任务中断。

实操避坑提示 #

• 不要偷懒直接用all_tables里的num_rows替代真实COUNT(1)结果：系统统计信息是定期采样的，不是实时值，你的需求是按CREATE_DATE分组的明细统计，必须逐表扫描真实数据才能保证口径准确。
• 批量任务尽量选业务低峰期执行：2000张表全量COUNT会产生大量IO，对在线库性能有影响，跑大表时可以加/*+ parallel(表名, 2) */并行提示减少执行时间，也可以按需过滤只统计近3-5年的CREATE_DATE范围，缩短扫描时长。
• 全量跑之前先抽10张小表验证计算口径，对比和你之前单表查询的结果是否一致，确认无误后再跑全量任务。

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