Oracle跨实例同表数据插入咨询：自定义ID填充需求

刚看到你的需求，正好之前处理过类似的跨Oracle实例数据迁移场景，给你几个实用的方案，你可以根据数据量大小、权限情况来选最合适的：

这是最直接的方式，不需要中间文件，跨实例直接操作，适合数据量不大的场景。

步骤： #

1. 在实例A中创建到B的数据库链接
   先确保A实例的用户有CREATE DATABASE LINK权限，然后执行以下SQL（替换成你的B实例真实信息）：

CREATE DATABASE LINK link_to_b
CONNECT TO b_user IDENTIFIED BY b_password
USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = b_host_ip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = b_sid)
    )
  )';

2. 执行插入语句，指定ID为'A11'
   明确列出除ID外的所有字段（避免因表结构顺序变化出错），直接从B的Test表取其他字段值：

-- 替换col1、col2为你Test表的实际字段名
INSERT INTO test (id, col1, col2, col3)
SELECT 'A11', col1, col2, col3
FROM test@link_to_b;

-- 一定要提交事务
COMMIT;

注意： #

• 如果B的Test表数据量很大，建议分批插入（比如用ROWNUM或者FETCH NEXT限制每次插入的行数），避免锁表或性能问题。
• 提前确认A的Test表是否有唯一约束/主键：如果ID是唯一键，所有行都用'A11'会触发约束冲突，只能插入1行，这时候得确认需求是不是要批量生成类似A11、A12的序列值（如果是这种情况，可以用序列+拼接字符串的方式，比如'A' || seq_test.NEXTVAL）。

适合数据量较大的场景，性能比DBLINK好，还能灵活修改字段值。

步骤： #

1. 从实例B导出Test表数据
   在B实例所在服务器执行导出命令：

expdp b_user/b_password@b_sid tables=test dumpfile=test_dump.dmp logfile=test_exp.log

导出的test_dump.dmp可以通过FTP/SCP传到A实例所在服务器。

2. 在实例A中创建自定义函数，用来设置ID值
   数据泵的REMAP_DATA参数需要用函数来修改字段值，所以先在A实例创建一个简单函数：

CREATE OR REPLACE FUNCTION set_fixed_id(old_id VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN 'A11'; -- 固定返回'A11'
END;
/

3. 导入数据到实例A的Test表
   在A实例所在服务器执行导入命令，指定REMAP_DATA来替换ID字段：

-- 如果A的Test表已存在，用TABLE_EXISTS_ACTION=APPEND追加数据，或者REPLACE清空后导入
impdp a_user/a_password@a_sid tables=test dumpfile=test_dump.dmp logfile=test_imp.log remap_data=test.id:set_fixed_id TABLE_EXISTS_ACTION=APPEND

适合需要先导出文本文件做预处理的场景，比如要先清洗数据再导入。

步骤： #

1. 从实例B导出Test表数据到文本文件
   在B实例中执行SQL导出（或者用SQL Developer/PL/SQL Developer可视化导出CSV）：

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL /tmp/test_data.csv
-- 只导出除ID外的字段，用逗号分隔
SELECT col1 || ',' || col2 || ',' || col3 FROM test;
SPOOL OFF

把导出的test_data.csv传到A实例所在服务器。

2. 编写SQL*Loader控制文件（test.ctl）
   控制文件里指定ID字段为固定值'A11'：

LOAD DATA
INFILE '/tmp/test_data.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  id CONSTANT 'A11', -- 固定设置ID为'A11'
  col1,
  col2,
  col3 -- 对应导出的字段顺序
)

3. 执行加载命令

sqlldr a_user/a_password@a_sid control=test.ctl log=test_ldr.log

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