SQL查询改写：执行生成的LOB列大小统计语句并按规则输出结果

以下方案适用于Oracle数据库，无需生成语句后手动执行，可直接返回你要求的统计结果：

方案1：PL/SQL匿名块（简单易用，适合客户端直接运行） #

无需创建额外数据库对象，执行后直接输出统计结果：

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    v_max_size_kb NUMBER;
BEGIN
    -- 打印表头
    DBMS_OUTPUT.PUT_LINE(
        RPAD('TABLE_NAME', 30) || 
        RPAD('COLUMN_NAME', 30) || 
        RPAD('DATA_TYPE', 10) || 
        'MAX(COLUMN_SIZE_KB)'
    );
    DBMS_OUTPUT.PUT_LINE(RPAD('=', 90, '='));

    -- 遍历所有LOB列并计算最大长度
    FOR rec IN (
        SELECT owner, table_name, column_name, data_type
        FROM dba_tab_cols 
        WHERE owner = '&SCHEMA' 
          AND data_type IN ('CLOB','BLOB','NCLOB')
    ) LOOP
        EXECUTE IMMEDIATE 
            'SELECT NVL(MAX(LENGTH(' || rec.column_name || '))/1024, 0) 
             FROM ' || rec.owner || '.' || rec.table_name
        INTO v_max_size_kb;

        -- 打印结果
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.table_name, 30) || 
            RPAD(rec.column_name, 30) || 
            RPAD(rec.data_type, 10) || 
            ROUND(v_max_size_kb, 2)
        );
    END LOOP;
END;
/

方案2：纯SQL查询（直接返回结果集，支持排序过滤） #

不需要编写PL/SQL逻辑，单条SQL直接返回结构化结果，默认按LOB最大大小降序、表名升序排序：

SELECT 
    table_name,
    column_name,
    data_type,
    ROUND(max_size_kb, 2) AS "MAX(COLUMN_SIZE)"
FROM (
    SELECT 
        t.table_name,
        t.column_name,
        t.data_type,
        TO_NUMBER(
            XMLQUERY(
                '/ROWSET/ROW/MAX_SIZE/text()' 
                PASSING XMLTYPE(
                    DBMS_XMLGEN.GETXML(
                        'SELECT NVL(MAX(LENGTH(' || t.column_name || '))/1024, 0) AS MAX_SIZE 
                         FROM ' || t.owner || '.' || t.table_name
                    )
                )
                RETURNING CONTENT
            )
        ) AS max_size_kb
    FROM dba_tab_cols t
    WHERE t.owner = '&SCHEMA'
      AND t.data_type IN ('CLOB','BLOB','NCLOB')
)
ORDER BY max_size_kb DESC, table_name;

说明 #

• 两种方案统计结果单位均为KB，无数据的LOB列默认返回0
• 若没有DBA权限，可将dba_tab_cols替换为all_tab_cols使用
• 运行前替换&SCHEMA为你要统计的目标 schema 名称即可

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