Oracle基于SQL Developer查询含指定值的表名及批量替换实现

前置注意事项 #

• 所有替换操作执行前，务必备份涉及的目标表数据，生产环境操作必须提前做变更评审
• 替换操作会触发表锁，尽量选业务低峰期执行，避免影响线上读写
• Oracle长事务会占用大量UNDO表空间，禁止一次性跑完全库更新，必须逐表控制事务大小

可控式实现方案（推荐生产环境使用） #

核心思路是先动态生成所有待执行的更新语句，人工核对匹配范围和变更量后再执行，避免误改无关数据。

1. 复用现有匹配扫描逻辑，生成带校验的可执行脚本，和你之前的检索脚本兼容：

-- 定义替换的旧值、新值
DEFINE old_val = 'oldDomain.com'
DEFINE new_val = 'newDomain.com'

WITH char_cols AS (
    SELECT /*+materialize */ owner, table_name, column_name
    FROM all_tab_columns
    WHERE data_type IN ('CHAR', 'VARCHAR2')
      -- 过滤系统schema，避免误操作系统内置表
      AND owner NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN')
)
SELECT 
  -- 生成正式更新语句
  'UPDATE "'||owner||'"."'||table_name||'" 
   SET "'||column_name||'" = REPLACE("'||column_name||'", '''&&old_val||''', '''&&new_val||''')
   WHERE UPPER("'||column_name||'") LIKE UPPER(''%&&old_val%%'');' AS update_statement,
  -- 生成匹配行数校验语句，提前统计待变更量
  'SELECT COUNT(*) AS match_rows FROM "'||owner||'"."'||table_name||'" 
   WHERE UPPER("'||column_name||'") LIKE UPPER(''%&&old_val%%'')' AS count_check_statement
FROM char_cols
ORDER BY table_name, column_name;

注意：如果待替换的旧值/新值包含单引号'，拼接生成SQL时需要将单引号转义为两个单引号，避免SQL语法报错。

2. 执行流程：
   • 先跑上面的查询语句，把所有生成的count_check_statement逐个执行，核对每个表/列的匹配行数，确认没有非预期的匹配结果
   • 核对完成后，逐个执行对应生成的update_statement，每执行完一条更新就手动执行COMMIT;提交事务
   • 全部更新完成后，再跑一遍你之前用的全库检索脚本，确认旧值已经被完全替换

测试环境简化实现 #

如果是测试环境不需要逐行核对，可以直接用PL/SQL块遍历执行，和你之前的dbms_xmlgen查询逻辑适配：

DECLARE
  v_old_val VARCHAR2(255) := 'oldDomain.com';
  v_new_val VARCHAR2(255) := 'newDomain.com';
  v_exec_sql VARCHAR2(4000);
  v_affected_rows NUMBER;
BEGIN
  FOR col_meta IN (
    SELECT owner, table_name, column_name
    FROM all_tab_columns
    WHERE data_type IN ('CHAR', 'VARCHAR2')
      AND owner NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN')
  ) LOOP
    -- 生成更新语句，用绑定变量避免特殊字符转义问题
    v_exec_sql := 'UPDATE "'||col_meta.owner||'"."'||col_meta.table_name||'" 
                  SET "'||col_meta.column_name||'" = REPLACE("'||col_meta.column_name||'", :1, :2)
                  WHERE UPPER("'||col_meta.column_name||'") LIKE UPPER(''%'||:3||'%'')';
    -- 执行动态更新
    EXECUTE IMMEDIATE v_exec_sql 
      INTO v_affected_rows
      USING v_old_val, v_new_val, v_old_val;
    -- 逐表提交，控制事务大小
    COMMIT;
    -- 打印变更日志，方便追溯
    DBMS_OUTPUT.PUT_LINE('已更新表：'||col_meta.owner||'.'||col_meta.table_name
      ||' 列：'||col_meta.column_name||' 影响行数：'||v_affected_rows);
  END LOOP;
END;
/

注意：这个自动执行块没有处理表上的约束、触发器冲突，碰到不满足更新条件的场景会直接报错中断，不建议在生产环境直接运行

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