Oracle数据库多Schema下各表记录数统计至独立表的实现方法

一、创建统计结果存储表 #

先创建一张表用来保存所有Schema各表的记录数，列名与需要统计的表名一一对应（示例为P、Q、R，实际按需扩展100+列）：

CREATE TABLE SCHEMA_TABLE_COUNTS (
    SCHEMANAME VARCHAR2(128) NOT NULL,
    P NUMBER,
    Q NUMBER,
    R NUMBER,
    -- 此处添加其余100+张表对应的列，列名与表名完全一致
    CONSTRAINT PK_SCHEMA_TABLE_COUNTS PRIMARY KEY (SCHEMANAME)
);

二、编写PL/SQL脚本批量统计并插入数据 #

通过遍历业务Schema和目标表，自动统计每张表的记录数并写入结果表，同时过滤系统内置Schema（如SYS、SYSTEM等）：

DECLARE
    v_schema VARCHAR2(128);
    v_sql VARCHAR2(4000);
    -- 定义需要统计的表名列表，替换为实际100+张表名
    TYPE tab_list IS TABLE OF VARCHAR2(128);
    v_tables tab_list := tab_list('P', 'Q', 'R');
BEGIN
    -- 清空历史统计数据（可选，根据需求决定是否保留）
    DELETE FROM SCHEMA_TABLE_COUNTS;
    
    -- 遍历所有业务Schema
    FOR rec IN (
        SELECT username AS schemaname
        FROM all_users
        WHERE username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN')
        -- 可添加Schema过滤规则，比如：AND username LIKE 'BUS_%'
    ) LOOP
        v_schema := rec.schemaname;
        v_sql := 'INSERT INTO SCHEMA_TABLE_COUNTS (SCHEMANAME';
        
        -- 拼接表名列
        FOR i IN 1..v_tables.COUNT LOOP
            v_sql := v_sql || ', ' || v_tables(i);
        END LOOP;
        
        v_sql := v_sql || ') VALUES (''' || v_schema || '''';
        
        -- 拼接每张表的记录数统计语句
        FOR i IN 1..v_tables.COUNT LOOP
            v_sql := v_sql || ', (SELECT COUNT(*) FROM ' || v_schema || '.' || v_tables(i) || ')';
        END LOOP;
        
        v_sql := v_sql || ')';
        
        -- 执行动态SQL
        EXECUTE IMMEDIATE v_sql;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('统计完成，共处理' || SQL%ROWCOUNT || '个Schema');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('执行出错：' || SQLERRM);
END;
/

三、查询结果表（格式化展示） #

直接查询结果表即可得到结构化的统计数据：

SELECT * FROM SCHEMA_TABLE_COUNTS ORDER BY SCHEMANAME;

查询结果示例：

注意事项 #

• 权限要求：执行脚本的用户需拥有SELECT ANY TABLE权限，或对所有目标Schema的表拥有SELECT权限。
• 性能优化：若Schema和表数量极大，可拆分脚本分批执行（比如按Schema名分段）；允许近似统计的话，可使用APPROX_COUNT_DISTINCT替代COUNT(*)提升速度。
• 定时更新：如需定期刷新数据，可通过DBMS_SCHEDULER创建定时任务，自动执行上述PL/SQL脚本。

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