如何在Oracle中获取全库含DT_LOAD列的所有表的最大日期？

你的原SQL存在两个核心问题：

1. ALL_TABLES视图不包含列信息，表的列元数据需要从ALL_TAB_COLUMNS视图查询；
2. 静态SQL无法动态遍历多个表执行查询，必须通过动态SQL实现需求。

以下是几种实用的实现方式：

方式1：PL/SQL块逐个查询输出 #

适合需要逐个查看单表结果的场景，执行后会在控制台输出每个表的最大日期：

SET SERVEROUTPUT ON;
DECLARE
    v_max_date DATE;
BEGIN
    -- 遍历所有含DT_LOAD列的表（排除系统用户，可按需调整）
    FOR rec IN (
        SELECT owner, table_name
        FROM all_tab_columns
        WHERE column_name = 'DT_LOAD'
          AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
    ) LOOP
        -- 动态执行查询，将结果存入变量
        EXECUTE IMMEDIATE 'SELECT MAX(dt_load) FROM ' || rec.owner || '.' || rec.table_name
            INTO v_max_date;
        -- 输出结果
        DBMS_OUTPUT.PUT_LINE('表: ' || rec.owner || '.' || rec.table_name || ' | 最大DT_LOAD日期: ' || v_max_date);
    END LOOP;
END;
/

方式2：生成UNION ALL静态SQL执行 #

先执行以下SQL生成拼接好的查询语句，复制生成的结果执行后即可得到统一的结果集：

SELECT 'SELECT ''' || owner || '.' || table_name || ''' AS table_name, MAX(dt_load) AS max_dt_load FROM ' || owner || '.' || table_name || 
       CASE WHEN ROWNUM < total THEN ' UNION ALL ' ELSE '' END AS sql_statement
FROM (
    SELECT owner, table_name, COUNT(*) OVER () AS total
    FROM all_tab_columns
    WHERE column_name = 'DT_LOAD'
      AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
)
ORDER BY owner, table_name;

方式3：用XMLAGG动态生成并执行SQL（直接返回结果集） #

无需手动拼接，一次性返回所有表的最大日期结果：

WITH target_tables AS (
    SELECT owner || '.' || table_name AS full_table_name
    FROM all_tab_columns
    WHERE column_name = 'DT_LOAD'
      AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
)
SELECT table_name, max_dt_load
FROM XMLTABLE(
    '/ROWSET/ROW'
    PASSING DBMS_XMLGEN.GETXMLTYPE(
        LISTAGG(
            'SELECT ''' || full_table_name || ''' AS table_name, MAX(dt_load) AS max_dt_load FROM ' || full_table_name,
            ' UNION ALL '
        ) WITHIN GROUP (ORDER BY full_table_name)
    )
    COLUMNS
        table_name VARCHAR2(150) PATH 'TABLE_NAME',
        max_dt_load DATE PATH 'MAX_DT_LOAD'
);

注意事项 #

• 确保当前数据库用户有查询目标表的权限，否则会抛出权限不足错误；
• 如果表名或所有者包含特殊字符（如空格、小写字母），需要用双引号包裹对象名，例如：'"' || owner || '"."' || table_name || '"'；
• 可根据实际需求调整排除的系统用户列表。

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