在Oracle中搜索所有表和所有列以查找字符串

Question

我需要在所有的表和列中搜索 Oracle 数据库中的字符串。我找到了以下在线查询语句，但是当我执行它时，会出现以下错误。

感谢您提供任何帮助。

ORA-06550: line 6, column 31:
PL/SQL: ORA-00904: "COLUMN_NAME": invalid identifier
ORA-06550: line 6, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 30:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 12, column 38:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 12, column 16:
PL/SQL: Statement ignored

BEGIN  
  FOR t IN (SELECT table_name, column_name FROM all_tables) LOOP   
   EXECUTE IMMEDIATE    
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'   
     INTO match_count  
       USING v_search_string; 
          IF match_count > 0 THEN 
               dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
           END IF; 
  END LOOP;
END;
/

- Jim

---

为什么不直接搜索数据库文件，而要使用grep之类的方式呢？这样至少可以获取文件/块偏移量，并找到相关的表。 - Adam Musch

Answer 1

至少需要查询ALL_TAB_COLUMNS，而不是ALL_TABLES。

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
      ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
      USING v_search_string; 
    IF match_count > 0 THEN 
      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;
/

如果你想查找一个字符串，那么你几乎肯定只想要寻找可以存储字符串的列。比如，在 DATE 列中搜索字符串是没有意义的。而且，除非你有关于 BLOB 列包含内容并能够解析 BLOB 列的二进制格式的大量先验知识，否则在 BLOB 列中搜索字符串是没有意义的。鉴于此，我猜想你更想要像这样的内容：

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner,
                   table_name, 
                   column_name 
              FROM all_tab_columns
             WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 
                                 'CLOB', 'NCLOB') ) 
  LOOP   
    BEGIN
      EXECUTE IMMEDIATE    
        'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
        ' WHERE '||t.column_name||' = :1'   
         INTO match_count  
        USING v_search_string; 
      IF match_count > 0 THEN 
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF; 
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
                              t.column_name || ' from ' || 
                              t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;
/

当然，这种方法极其缓慢--您需要为表中的每个字符串列扫描一次整个表。对于较大的表和适量数量的字符串列，这可能需要相当长的时间。

- Justin Cave

---

那个可以运行，但是有些查询会给我下面的错误：ORA-00942: 表或视图不存在 ORA-06512: 在第12行我该如何使它继续执行或忽略这个错误？ - Jim

这可能是一个愚蠢的问题，但是这个搜索查询是区分大小写的还是不区分大小写的？ - Mikhail

@Mikhail - 一般来说，这将进行区分大小写的搜索（在Oracle中，查询通常是区分大小写的）。但是，如果您已为会话设置了NLS_SORT和NLS_COMP以执行不区分大小写的搜索，则此操作将执行不区分大小写的搜索。 - Justin Cave

@JustinCave 我该如何更改它们？我尝试使用alter session set NLS_COMP=ANSI + alter session NLS_SORT=BINARY_CI，但似乎对我不起作用。另外我注意到你的查询没有返回我正在搜索的值，而这些值肯定存在于数据库中（我的搜索字符串看起来像'%value1%'）... 这个查询是通用的还是我需要为我的数据库做一些调整？ - Mikhail

@Mikhail - 不区分大小写的搜索无法使用 LIKE 运算符实现，您需要正确指定大小写。当然，您可以修改查询为 WHERE lower(' || t.column_name || ') like lower(%1)。或者，您可以使用 regexp_like 函数，该函数接受一个大小写敏感性参数作为其第三个参数。 - Justin Cave