Oracle数据库查找含邮箱值的表和字段（脚本无输出排查）

问题场景 #

数据库内约2000张表，需定位存储邮箱格式（xxx@xxx.xxx）的表与字段（字段名未必含email标识），但参考编写的PL/SQL脚本运行后无任何输出。

原脚本 #

DECLARE
    l_cmd     VARCHAR2 (2000);
    l_found   INTEGER;
BEGIN
    FOR eachcol IN (  SELECT *
                        FROM all_tab_cols a
                       WHERE a.data_type = 'VARCHAR2'
                         AND owner = 'SEARCHSCHEMANAME'
                    ORDER BY table_name, column_name)
    LOOP
        l_cmd   :=
               'select count(*) c from '
            || eachcol.owner
            || '.'
            || eachcol.table_name
            || ' where '
            || LOWER (eachcol.column_name)
            || q'[ LIKE '%@%.%' AND ROWNUM = 1]';

        EXECUTE IMMEDIATE l_cmd INTO l_found;

        IF l_found > 0
        THEN
            DBMS_OUTPUT.put_line (
                   RPAD (eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name, 92)
                || ' may contain email addresses'
            );
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (l_cmd);
        DBMS_OUTPUT.put_line (SQLERRM);
        RAISE;
END;

问题排查与修复 #

1. 列名大小写匹配错误（核心问题） #

Oracle默认创建的字段名为大写，all_tab_cols视图中column_name字段存储的也是大写值。原脚本中使用LOWER(eachcol.column_name)将列名转为小写，拼接后SQL的WHERE子句会引用小写列名，而实际数据库中不存在该列，导致查询返回count(*)=0，因此无输出。

修复方式：
直接使用原列名，并通过双引号包裹，兼容大小写敏感的列名（若有）：

l_cmd   :=
       'select count(*) c from '
    || eachcol.owner
    || '.'
    || eachcol.table_name
    || ' where "'
    || eachcol.column_name
    || '" LIKE ''%@%.%'' AND ROWNUM = 1';

2. 数据类型范围过窄 #

原脚本仅检查VARCHAR2类型字段，但邮箱也可能存储在CHAR或CLOB类型字段中。若需覆盖更多场景，需修改all_tab_cols的筛选条件：

WHERE a.data_type IN ('VARCHAR2', 'CHAR', 'CLOB')

注：CLOB类型使用LIKE需先转为字符类型，可调整为DBMS_LOB.SUBSTR("列名", 1000) LIKE '%@%.%'（根据实际数据长度调整截取长度）

3. 权限验证 #

确保当前用户对SEARCHSCHEMANAME下的目标表拥有SELECT权限，否则动态执行SQL时会触发异常。若未触发异常，说明权限无问题；若有隐藏异常，可在循环内增加异常捕获，避免单张表报错导致整个脚本终止：

LOOP
    BEGIN
        l_cmd   := ...; -- 拼接SQL
        EXECUTE IMMEDIATE l_cmd INTO l_found;
        IF l_found > 0 THEN
            DBMS_OUTPUT.put_line(...);
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('Error in ' || eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name);
            DBMS_OUTPUT.put_line(SQLERRM);
    END;
END LOOP;

4. 优化查询逻辑 #

使用EXISTS替代COUNT(*)+ROWNUM，效率更高：

l_cmd   :=
       'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM '
    || eachcol.owner
    || '.'
    || eachcol.table_name
    || ' WHERE "'
    || eachcol.column_name
    || '" LIKE ''%@%.%'' AND ROWNUM = 1)';

执行后若l_found=1则表示存在匹配数据。

修正后完整脚本 #

DECLARE
    l_cmd     VARCHAR2 (2000);
    l_found   INTEGER;
BEGIN
    FOR eachcol IN (  SELECT *
                        FROM all_tab_cols a
                       WHERE a.data_type IN ('VARCHAR2', 'CHAR')
                         AND owner = 'SEARCHSCHEMANAME'
                    ORDER BY table_name, column_name)
    LOOP
        BEGIN
            l_cmd   :=
                   'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM '
                || eachcol.owner
                || '.'
                || eachcol.table_name
                || ' WHERE "'
                || eachcol.column_name
                || '" LIKE ''%@%.%'' AND ROWNUM = 1)';

            EXECUTE IMMEDIATE l_cmd INTO l_found;

            IF l_found = 1
            THEN
                DBMS_OUTPUT.put_line (
                       RPAD (eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name, 92)
                    || ' may contain email addresses'
                );
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line('Failed to check ' || eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name);
                DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Global error: ' || SQLERRM);
        RAISE;
END;

内容的提问来源于stack exchange，提问作者Jyoti Prakash Mallick