Oracle全庫表對象與數據搜索：動態PL/SQL掃描實戰

核心需求

在不知表結構前提下，按數據類型(如VARCHAR2)與關鍵字(如'工行')掃描全庫所有表的所有列，定位包含目標值的記錄並輸出可直接執行的查詢語句。

完整掃描腳本 (可直接在SQL*Plus/PLSQL Developer執行)

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 300
SET LONG 100000

DECLARE
  -- 1. 目標表游標 (按所有者模式過濾)
  CURSOR c_tables(p_owner_like VARCHAR2) IS
    SELECT owner, table_name
    FROM all_tables
    WHERE owner LIKE p_owner_like
    ORDER BY owner, table_name;

  -- 2. 目標列游標 (按數據類型過濾)
  CURSOR c_columns(p_owner VARCHAR2, p_table VARCHAR2, p_type VARCHAR2) IS
    SELECT column_name
    FROM all_tab_columns
    WHERE owner = p_owner
      AND table_name = p_table
      AND data_type = p_type;

  v_sql       VARCHAR2(4000);
  v_cnt       NUMBER;
  v_type      VARCHAR2(30) := UPPER('VARCHAR2');  -- 目標類型
  v_value     VARCHAR2(4000) := '工行';           -- 目標關鍵字
  v_use_like  BOOLEAN := TRUE;                    -- TRUE=LIKE模糊, FALSE=精確
  v_logic_op  VARCHAR2(10) := '=';
  v_pattern   VARCHAR2(4000);
BEGIN
  -- 構造匹配模式
  IF v_use_like THEN
    v_logic_op := ' LIKE ';
    v_pattern := '%' || v_value || '%';
  ELSE
    v_logic_op := ' = ';
    v_pattern := '''' || v_value || '''';
  END IF;

  -- 針對字符類型自動轉大寫匹配 (可選)
  IF v_type IN ('VARCHAR2','CHAR','VARCHAR','NCHAR','NVARCHAR2') THEN
    v_pattern := UPPER(v_pattern);
  END IF;

  DBMS_OUTPUT.PUT_LINE('=== Start Scan: Type=' || v_type || ', Value=' || v_value || ' ===');

  -- 3. 雙層循環掃描
  FOR t IN c_tables('%HS_%') LOOP  -- 所有者模式，如 'APP_%', 'SYS', '%'
    FOR c IN c_columns(t.owner, t.table_name, v_type) LOOP
      -- 動態構造計數SQL
      v_sql := 'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
               ' WHERE UPPER(' || c.column_name || ')' || v_logic_op || v_pattern;

      BEGIN
        EXECUTE IMMEDIATE v_sql INTO v_cnt;
        IF v_cnt > 0 THEN
          -- 輸出可直接執行的明細查詢
          DBMS_OUTPUT.PUT_LINE(
            'SELECT * FROM ' || t.owner || '.' || t.table_name ||
            ' WHERE UPPER(' || c.column_name || ')' || v_logic_op || v_pattern || '; -- CNT=' || v_cnt
          );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('-- ERROR on ' || t.owner || '.' || t.table_name || '.' || c.column_name || ': ' || SQLERRM);
      END;
    END LOOP;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('=== Scan Completed ===');
END;
/

關鍵參數配置說明

性能與安全優化建議

進階版：生成統一報表表

CREATE GLOBAL TEMPORARY TABLE scan_results (
  owner_name     VARCHAR2(128),
  table_name     VARCHAR2(128),
  column_name    VARCHAR2(128),
  match_count    NUMBER,
  sample_sql     VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

-- 掃描循環中替換 DBMS_OUTPUT 為：
INSERT INTO scan_results VALUES (t.owner, t.table_name, c.column_name, v_cnt, v_sql);
COMMIT; -- 自主事務或批量提交

-- 掃描結束後
SELECT * FROM scan_results ORDER BY match_count DESC;

適用場景：
• 數據治理：尋找敏感數據(身份證/手機/銀行卡)分佈表
• 影響分析：某業務值(如機構代碼'工行')涉及哪些表/列
• 遷移評估：舊系統關鍵字段在新系統對應表分佈
• 審計追蹤：異常值來源溯源

⚠️ 生產庫執行必須：
1. 低峰期/維護窗口
2. 並行度控制(建議≤4會話)
3. 監控V$SESSION_WAIT避免全表掃描沖擊核心業務
4. 結束後收集統計信息修復可能失效的游標計劃