Oracle跨库数据同步：表变量声明与全库批量同步方法问询

1. Oracle SQL/PL/SQL中是否可以声明表类型变量？ #

普通SQL语句里不能直接声明表类型变量，但在PL/SQL块中完全支持，常见实现方式有两种：

• 基于现有表结构定义：

  DECLARE
      -- 定义匹配emp表行结构的表类型
      TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;
      -- 声明表类型变量
      v_emp_tab emp_tab_type;
  BEGIN
      -- 批量将查询结果存入表变量
      SELECT * BULK COLLECT INTO v_emp_tab FROM emp WHERE deptno = 10;
  END;
  /
  

• 自定义记录+集合类型：

  DECLARE
      -- 自定义单行记录结构
      TYPE emp_rec_type IS RECORD (
          empno emp.empno%TYPE,
          ename emp.ename%TYPE
      );
      -- 基于记录类型定义表类型
      TYPE emp_tab_type IS TABLE OF emp_rec_type;
      v_emp_tab emp_tab_type;
  BEGIN
      SELECT empno, ename BULK COLLECT INTO v_emp_tab FROM emp WHERE deptno = 20;
  END;
  /
  

你代码里的DECLARE tab table;属于语法错误，table是Oracle关键字，不能直接用来声明变量类型，必须先定义合法的自定义类型或使用内置集合类型。

2. 遍历数据库同步所有表数据的方法 #

有三种主流方案，按需选择：

方案一：PL/SQL动态SQL批量处理 #

通过查询数据字典获取表名，循环生成同步语句执行（假设源用户为RA012345，目标用户为TECH）：

DECLARE
    v_src_schema VARCHAR2(30) := 'RA012345';
    v_dest_schema VARCHAR2(30) := 'TECH';
    v_sql VARCHAR2(1000);
BEGIN
    -- 遍历源用户下的所有用户表（排除系统表）
    FOR rec IN (SELECT table_name FROM all_tables WHERE owner = UPPER(v_src_schema)) LOOP
        -- 清空目标表
        v_sql := 'DELETE FROM ' || v_dest_schema || '.' || rec.table_name;
        EXECUTE IMMEDIATE v_sql;
        
        -- 同步源表数据到目标表
        v_sql := 'INSERT INTO ' || v_dest_schema || '.' || rec.table_name || ' SELECT * FROM ' || v_src_schema || '.' || rec.table_name;
        EXECUTE IMMEDIATE v_sql;
        
        COMMIT;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('同步表 ' || rec.table_name || ' 失败：' || SQLERRM);
END;
/

注意：需拥有源表查询权限、目标表增删权限，大表同步时性能一般。

方案二：Oracle数据泵（EXPDP/IMPDP） #

官方推荐的高效全量迁移工具，适合大规模数据同步：

1. 导出源用户数据：

   expdp your_user/your_pwd@source_db schemas=RA012345 dumpfile=schema_dump.dmp logfile=exp_log.log
   

2. 导入到目标用户（自动映射用户）：

   impdp your_user/your_pwd@target_db dumpfile=schema_dump.dmp logfile=imp_log.log remap_schema=RA012345:TECH
   

方案三：增量同步工具（如GoldenGate） #

如果需要长期实时增量同步，可使用Oracle GoldenGate，实现跨库数据实时复制，但配置复杂度较高。

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