使用SQL*Plus进行Oracle数据迁移的指南

一、数据迁移前的准备工作

权限确认：确保具备源数据库和目标数据库的读权限（导出数据）及写权限（导入数据）。若迁移其他用户的对象（如表、视图），需具备EXP_FULL_DATABASE（导出）或IMP_FULL_DATABASE（导入）权限。
工具与环境配置：
- 在源/目标服务器上安装Oracle客户端，确保sqlplus、expdp（数据泵导出）、impdp（数据泵导入）命令可用。
- 配置tnsnames.ora文件（位于$ORACLE_HOME/network/admin），添加源/目标数据库的连接别名（如SOURCE_DB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=source_svc)))），便于sqlplus通过别名连接。
数据库结构理解：通过USER_TABLES、ALL_CONSTRAINTS、ALL_INDEXES等视图，明确源数据库中需迁移的表、索引、约束、触发器等对象信息，避免遗漏关键数据。
迁移需求定义：明确需迁移的表范围（全表/部分表）、数据量大小（评估迁移时间）、迁移时间窗口（尽量选择业务低峰期）、是否允许目标数据库写入（如是否允许INSERT冲突）。

二、使用SQL*Plus导出数据（平面文件方式）

若需将数据导出为CSV、TXT等平面文件（适用于跨数据库迁移或后续处理），可通过SQL*Plus的SPOOL命令实现：

编写导出脚本（如export_emp.sql）：

SET PAGESIZE 0              -- 不显示分页页眉页脚
SET FEEDBACK OFF            -- 不显示查询结果的反馈信息（如"X rows selected"）
SET HEADING OFF             -- 不显示列标题
SET MARKUP HTML OFF         -- 不生成HTML格式
SET ECHO OFF                -- 不显示执行的SQL语句
SET VERIFY OFF              -- 不显示变量替换后的语句
SET TERMOUT OFF             -- 不显示命令输出到屏幕
SET TRIMSPOOL ON            -- 去除输出内容的尾部空格
SPOOL /path/to/output_emp.csv  -- 指定输出文件路径（Linux/Unix用绝对路径）
SELECT emp_id || ',' || emp_name || ',' || salary FROM emp;  -- 使用分隔符拼接字段（CSV格式）
SPOOL OFF                   -- 结束输出，关闭文件

执行导出脚本：登录sqlplus后，运行@/path/to/export_emp.sql，即可将emp表的数据导出为CSV文件。

三、使用数据泵工具（EXPDP/IMPDP）迁移数据

数据泵（Data Pump）是Oracle推荐的快速数据迁移工具，支持增量迁移、并行处理、压缩等功能，效率远高于传统EXP/IMP。需通过操作系统命令行执行（而非sqlplus内部），但sqlplus可用于创建数据泵所需的目录对象。

创建数据泵目录对象：
登录sqlplus（需具备CREATE ANY DIRECTORY权限），执行以下命令创建目录对象（关联操作系统路径）：
```
CREATE DIRECTORY export_dir AS '/u01/app/oracle/dumpfiles';
CREATE DIRECTORY import_dir AS '/u01/app/oracle/dumpfiles';
```
执行SELECT * FROM dba_directories;验证目录是否创建成功。
导出数据（EXPDP）：
在操作系统命令行（切换至oracle用户，因普通用户无权限执行数据泵命令）运行：
```
expdp 'scott/tiger@source_db' schemas=scott directory=export_dir dumpfile=scott_data.dmp logfile=expdp_scott.log
```
参数说明：
- schemas=scott：指定导出的用户方案（仅导出scott用户的对象）；
- directory=export_dir：指定导出文件的存储目录（需与sqlplus中创建的目录对象一致）；
- dumpfile=scott_data.dmp：导出文件名；
- logfile=expdp_scott.log：导出日志文件名。
传输导出文件：将生成的.dmp文件从源服务器传输至目标服务器（使用scp、FTP等工具），例如：
```
scp /u01/app/oracle/dumpfiles/scott_data.dmp target_user@target_host:/u01/app/oracle/dumpfiles/
```
导入数据（IMPDP）：
在目标服务器的操作系统中运行：
```
impdp 'system/manager@target_db' schemas=scott directory=import_dir dumpfile=scott_data.dmp logfile=impdp_scott.log
```
若需将数据导入至目标数据库的其他用户（如target_user），需添加touser参数：
```
impdp 'system/manager@target_db' schemas=scott directory=import_dir dumpfile=scott_data.dmp logfile=impdp_scott.log touser=target_user
```
参数说明：
- touser=target_user：指定目标用户（需具备IMP_FULL_DATABASE权限）；
- 其他参数含义与EXPDP一致。

四、使用SQL*Plus直接迁移数据（跨数据库）

若源/目标数据库均在同一网络且已配置tnsnames.ora，可通过SQL*Plus的COPY命令直接迁移数据（无需中间文件），适用于小批量数据迁移：

-- 从源数据库复制数据至目标数据库（创建目标表并插入数据）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      CREATE emp_target 
      USING SELECT * FROM emp_source;

-- 向目标数据库现有表插入数据（目标表需提前存在）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      INSERT emp_target 
      USING SELECT * FROM emp_source;

-- 替换目标表（删除目标表并重新创建，再插入数据）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      REPLACE emp_target 
      USING SELECT * FROM emp_source;

参数说明：

FROM/TO：指定源/目标数据库的连接信息（username/password@tns_alias）；
CREATE：目标表不存在时创建表（结构与源表一致）；
INSERT：向目标表插入数据（目标表需存在）；
REPLACE：删除目标表并重新创建（适用于表结构变更场景）；
USING：指定源数据的查询语句。

五、数据验证与清理

数据验证：
- 数量验证：对比源/目标表的行数（SELECT COUNT(*) FROM emp;），确保数据量一致；
- 内容验证：抽样查询关键字段（如SELECT emp_id, emp_name FROM emp WHERE emp_id = 100;），确认数据准确性；
- 约束验证：检查目标表的约束（如主键、唯一键、外键）是否生效（SELECT * FROM user_constraints WHERE table_name = 'EMP';）。
清理工作：
- 删除临时平面文件（如/path/to/output_emp.csv）；
- 删除数据泵导出文件（如scott_data.dmp）；
- 删除sqlplus日志文件（如export_emp.log），释放存储空间。

注意事项

字符集一致性：源/目标数据库的字符集需一致（如AL32UTF8），否则可能导致中文乱码（EXPDP/IMPDP时会自动检查字符集，不一致时报错）。
权限问题：EXPDP/IMPDP需在oracle用户下执行，普通用户无法调用；COPY命令需源/目标数据库的SELECT（源）和INSERT（目标）权限。
大对象处理：若表包含BLOB、CLOB等大对象，建议使用EXPDP/IMPDP（支持大对象高效迁移），而非COPY或平面文件。
错误处理：迁移过程中若出现错误（如ORA-39002: invalid operation、ORA-12154: TNS:could not resolve），需检查日志文件（如expdp_scott.log）定位问题（如权限不足、目录不存在、TNS配置错误）。

0 赞

0 踩