如何用PL/SQL将多个Oracle对象迁移至其他用户模式？

一、PL/SQL批量迁移实现思路（适配80/20原则） #

核心是通过Oracle数据字典生成动态SQL，批量处理带_BAK（含日期后缀）的对象，无需逐个传参：

• 先从ALL_OBJECTS筛选出源模式下名称匹配%_BAK%的对象，覆盖大部分备份场景
• 按对象类型生成对应迁移语句，完成后删除原对象：
  • 表：用CREATE TABLE <目标模式>.<表名> AS SELECT * FROM <源模式>.<表名>迁移结构和数据，80/20原则下可暂忽略复杂约束
  • 视图：从ALL_VIEWS获取视图定义，拼接CREATE OR REPLACE VIEW语句迁移
  • 序列：从ALL_SEQUENCES获取序列属性（起始值、步长等），生成创建语句
  • 存储过程/函数：从ALL_SOURCE提取源码，拼接CREATE OR REPLACE语句迁移

示例核心代码片段：

CREATE OR REPLACE PROCEDURE MIGRATE_BAK_OBJECTS(p_from_user IN VARCHAR2, p_to_user IN VARCHAR2) IS
  v_sql VARCHAR2(4000);
BEGIN
  -- 迁移并删除备份表
  FOR rec IN (SELECT object_name FROM all_objects WHERE owner = UPPER(p_from_user) AND object_type = 'TABLE' AND object_name LIKE '%_BAK%') LOOP
    v_sql := 'CREATE TABLE ' || UPPER(p_to_user) || '.' || rec.object_name || ' AS SELECT * FROM ' || UPPER(p_from_user) || '.' || rec.object_name;
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'DROP TABLE ' || UPPER(p_from_user) || '.' || rec.object_name;
    EXECUTE IMMEDIATE v_sql;
  END LOOP;

  -- 迁移并删除备份视图
  FOR rec IN (SELECT object_name FROM all_objects WHERE owner = UPPER(p_from_user) AND object_type = 'VIEW' AND object_name LIKE '%_BAK%') LOOP
    SELECT 'CREATE OR REPLACE VIEW ' || UPPER(p_to_user) || '.' || object_name || ' AS ' || TEXT INTO v_sql FROM all_views WHERE owner = UPPER(p_from_user) AND view_name = rec.object_name;
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'DROP VIEW ' || UPPER(p_from_user) || '.' || rec.object_name;
    EXECUTE IMMEDIATE v_sql;
  END LOOP;

  -- 序列、存储过程/函数可按相同逻辑扩展
END;
/

二、更高效的替代工具推荐 #

• Oracle Datapump（expdp/impdp）：并非完全不可行，通过INCLUDE/EXCLUDE参数结合模糊匹配即可批量处理：

  1. 先导出不含备份对象的源模式（用于生产）：

     expdp <源用户>/<密码>@<库名> schemas=<源用户> exclude=TABLE:"LIKE '%_BAK%'",VIEW:"LIKE '%_BAK%'",SEQUENCE:"LIKE '%_BAK%'",PROCEDURE:"LIKE '%_BAK%'",FUNCTION:"LIKE '%_BAK%'" dumpfile=prod_exp.dmp logfile=prod_exp.log
     

  2. 单独导出备份对象并迁移到归档模式：

     expdp <源用户>/<密码>@<库名> schemas=<源用户> include=TABLE:"LIKE '%_BAK%'",VIEW:"LIKE '%_BAK%'",SEQUENCE:"LIKE '%_BAK%'",PROCEDURE:"LIKE '%_BAK%'",FUNCTION:"LIKE '%_BAK%'" dumpfile=bak_exp.dmp logfile=bak_exp.log
     impdp <归档用户>/<密码>@<库名> remap_schema=<源用户>:<归档用户> dumpfile=bak_exp.dmp logfile=bak_imp.log
     

  这种方式稳定性更高，适合批量处理全类型对象。

• PL/SQL Developer：支持跨模式批量复制对象，右键选中多个带_BAK后缀的对象，选择「Copy to other user」即可完成迁移，比SQL Developer更灵活。

三、关键注意事项 #

• 迁移前务必备份所有备份对象，避免误操作
• 处理带外键的表时，需调整迁移顺序或先禁用外键
• 存储过程/函数迁移后，检查依赖对象的引用是否正确，必要时修改模式前缀

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