【Oracle】全テーブルから任意の文字列を部分一致で検索する

はじめに

この前 Oracle DB のテーブル内から特定の文字列を探し出さなきゃいけなかったことがあり、対象のテーブルとカラムを探し出す PL/SQL 作って実行しました。

実行環境

• Oracle：19c Standard Edition 2 Release 19.0.0.0.0
• DBeaver：24.0.5.202405191503

特定の文字列を横断検索するPL/SQL

hoge をいれかえたら検索できます。

OracleDB内の全テーブル/カラムから'hoge'を探すPL/SQL

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  match_count INTEGER;
  v_search_string VARCHAR2(255) := 'hoge';
BEGIN
  FOR t IN (SELECT atc.owner, atc.table_name, atc.column_name 
            FROM all_tab_columns atc
            JOIN all_tables at ON atc.table_name = at.table_name AND atc.owner = at.owner
            WHERE atc.data_type LIKE '%CHAR%') LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM "' || t.owner || '"."' || t.table_name ||
        '" WHERE "' || t.column_name || '" LIKE :1' INTO match_count
        USING '%' || v_search_string || '%';
      IF match_count > 0 THEN
        dbms_output.put_line('Table: ' || t.table_name || ', Column: ' || t.column_name || ', Matches: ' || TO_CHAR(match_count));
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error occurred > Table: ' || t.table_name || ', Column: ' || t.column_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;

実行結果

んへぇーすげー！

Table: HOGE_TBL, Column: HOGE_VALUE, Matches: 1
Error occurred > Table: FUGA, Column: FUGA - ORA-XXXXX: エラーメッセージ
Error occurred > Table: PIYO, Column: PIYO - ORA-XXXXX: エラーメッセージ
Table: HOGE_REQUEST, Column: HOGE_URL, Matches: 4140
Table: HOGE_EMP, Column: NAME, Matches: 5

注意点

• DBの中身が宇宙（巨大の意）すぎて実行に1時間かかりました
• ビュー / マテリアライズド・ビューは検索対象外にしています

参考