Oracle数据库间数据迁移：如何用存储过程、游标等脚本复制表数据

首先要明确：跨Oracle数据库复制数据的核心前提是创建数据库链接（DBLINK），目标库通过它访问源库的表数据。先创建DBLINK的示例：

-- 在目标数据库创建指向源库的DBLINK
CREATE DATABASE LINK source_db_link
CONNECT TO source_user IDENTIFIED BY source_password
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 源库IP)(PORT = 1521))
    (CONNECT_DATA = (SID = 源库SID))
)';

方案1：游标逐行遍历插入（适合小表） #

如果是数据量不大的表，用游标逐行读取源库数据，插入目标表，逻辑简单易调试。以下是完整存储过程示例：

CREATE OR REPLACE PROCEDURE copy_table_data_small
IS
    -- 定义与源表字段匹配的变量
    v_id NUMBER;
    v_name VARCHAR2(100);
    v_create_date DATE;
    -- 定义游标，从源库表取数
    CURSOR c_source_data IS
        SELECT id, name, create_date
        FROM source_table@source_db_link; -- 用DBLINK访问源表
BEGIN
    -- 打开游标
    OPEN c_source_data;
    LOOP
        -- 逐行读取游标数据到变量
        FETCH c_source_data INTO v_id, v_name, v_create_date;
        -- 游标遍历结束则退出循环
        EXIT WHEN c_source_data%NOTFOUND;
        
        -- 插入目标表
        INSERT INTO target_table(id, name, create_date)
        VALUES(v_id, v_name, v_create_date);
    END LOOP;
    -- 提交事务
    COMMIT;
    -- 关闭游标
    CLOSE c_source_data;
    
    DBMS_OUTPUT.PUT_LINE('数据复制完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        CLOSE c_source_data;
        DBMS_OUTPUT.PUT_LINE('复制失败：' || SQLERRM);
        RAISE;
END;
/

调用方式：

SET SERVEROUTPUT ON;
EXEC copy_table_data_small;

方案2：批量游标+FORALL（适合大数据量） #

大数据量下逐行插入性能极低，用BULK COLLECT批量读取游标数据，再用FORALL批量插入，能大幅减少数据库上下文切换次数，提升效率。

CREATE OR REPLACE PROCEDURE copy_table_data_large
IS
    -- 定义与源表结构匹配的记录类型
    TYPE source_rec_type IS RECORD(
        id NUMBER,
        name VARCHAR2(100),
        create_date DATE
    );
    -- 定义记录类型的集合
    TYPE source_rec_tab IS TABLE OF source_rec_type;
    v_source_data source_rec_tab;
    -- 定义游标
    CURSOR c_source_data IS
        SELECT id, name, create_date
        FROM source_table@source_db_link;
    -- 批量大小（可根据内存调整，比如1000/5000）
    v_batch_size CONSTANT PLS_INTEGER := 1000;
BEGIN
    OPEN c_source_data;
    LOOP
        -- 批量读取游标数据到集合，每次取v_batch_size条
        FETCH c_source_data BULK COLLECT INTO v_source_data LIMIT v_batch_size;
        -- 集合为空则退出
        EXIT WHEN v_source_data.COUNT = 0;
        
        -- FORALL批量插入，比循环INSERT快N倍
        FORALL i IN 1..v_source_data.COUNT
            INSERT INTO target_table(id, name, create_date)
            VALUES(v_source_data(i).id, v_source_data(i).name, v_source_data(i).create_date);
        
        -- 每批提交一次，避免大事务占用过多资源
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('已完成' || v_source_data.COUNT || '条数据复制');
    END LOOP;
    CLOSE c_source_data;
    
    DBMS_OUTPUT.PUT_LINE('全部数据复制完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        IF c_source_data%ISOPEN THEN
            CLOSE c_source_data;
        END IF;
        DBMS_OUTPUT.PUT_LINE('复制失败：' || SQLERRM);
        RAISE;
END;
/

调用方式：

SET SERVEROUTPUT ON;
EXEC copy_table_data_large;

关键注意事项 #

• 表结构兼容：目标表字段数量、数据类型要与源表匹配，或者在SELECT时做类型转换（比如TO_CHAR(create_date, 'YYYY-MM-DD')）
• 权限要求：目标库用户需要有CREATE DATABASE LINK权限，同时能访问源库的source_user下的source_table（源库需给source_user授予SELECT ON source_table TO 目标库用户）
• 特殊字段处理：如果源表有CLOB/BLOB类型，批量处理时要确保集合类型支持；如果有自增主键（比如SEQUENCE），要避免主键冲突，可修改INSERT语句用目标库的SEQUENCE
• 事务控制：大数据量一定要分批次提交，否则可能导致回滚段溢出
• 性能优化：源表如果数据量极大，可在游标SELECT中加WHERE条件分批次同步（比如按日期范围），避免一次性读取全表数据占用过多内存

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