Debian中sqlplus数据导入导出方法

Debian下使用 SQL*Plus 的数据导入导出

一 常用方式与适用场景

• Data Pump 导出/导入 expdp/impdp：面向Oracle 10g+的高性能逻辑备份，适合全库/按用户/按表空间/按表迁移与恢复，支持压缩、并行、增量等能力。
• 传统导出/导入 exp/imp：兼容老版本 Oracle，适合小规模或兼容性迁移，语法简单，但性能与功能不及 Data Pump。
• SQL*Plus SPOOL 导出为文本/CSV：用 SQL 定制导出格式，生成CSV/TSV等文本数据，便于对接外部系统或批处理。
• SQL*Plus 执行 SQL 脚本：通过 @/path/file.sql 运行 DDL/DML 脚本，适合初始化结构、批量数据脚本执行。
• SQL*Loader(外部表) 导入文本：配合 SPOOL 生成的文本，用 sqlldr 高效批量导入，支持复杂分隔符、截断、日期格式等。

二 操作步骤与示例

• 前置准备

  • 安装 Oracle 客户端或 Instant Client，确保 sqlplus / expdp / impdp / sqlldr 在 PATH 中可用。
  • 对于 expdp/impdp，需在数据库创建 DIRECTORY 对象并赋权（示例中将目录对象设为 DATA_PUMP_DIR）：
    • 以管理员登录：sqlplus / as sysdba
    • 建目录对象：CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS ‘/opt/oracle/dp’;
    • 授权：GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO your_user;
    • 注意：导出/导入文件实际存放在数据库服务器的该目录，而非客户端本地。

• Data Pump 导出与导入

  • 全库导出（示例）
    • expdp system/password@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_$(date +%F).dmp LOGFILE=full_$(date +%F).log FULL=Y PARALLEL=2 COMPRESSION=ALL
  • 按用户导出
    • expdp scott/tiger@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=scott_$(date +%F).dmp LOGFILE=scott_$(date +%F).log SCHEMAS=scott
  • 按表导出
    • expdp scott/tiger@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=tabs_$(date +%F).dmp LOGFILE=tabs_$(date +%F).log TABLES=emp,dept
  • 导入（示例）
    • 全库：impdp system/password@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_2026-01-03.dmp LOGFILE=imp_full_$(date +%F).log FULL=Y
    • 按用户：impdp system/password@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=scott_2026-01-03.dmp LOGFILE=imp_scott_$(date +%F).log REMAP_SCHEMA=scott:scott_new
    • 按表：impdp scott/tiger@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=tabs_2026-01-03.dmp LOGFILE=imp_tabs_$(date +%F).log TABLES=emp,dept REMAP_SCHEMA=scott:scott_new
  • 提示：导入前确认目标用户/表空间存在，必要时使用 REMAP_SCHEMA / REMAP_TABLESPACE；大数据量可结合 PARALLEL 与 TABLE_EXISTS_ACTION=TRUNCATE/REPLACE/SKIP。

• 传统 exp/imp 导出与导入

  • 导出
    • exp scott/tiger@orcl FILE=/opt/oracle/backup/scott.dmp LOG=/opt/oracle/backup/scott.log OWNER=scott
  • 导入
    • 全量：imp system/password@orcl FILE=/opt/oracle/backup/scott.dmp LOG=/opt/oracle/backup/imp_scott.log FULL=Y
    • 按用户映射：imp system/password@orcl FILE=/opt/oracle/backup/scott.dmp LOG=/opt/oracle/backup/imp_scott.log FROMUSER=scott TOUSER=scott_new
    • 仅导入部分表：imp scott/tiger@orcl FILE=/opt/oracle/backup/scott.dmp LOG=/opt/oracle/backup/imp_tabs.log TABLES=(emp,dept) IGNORE=Y
  • 说明：IGNORE=Y 在目标表已存在时忽略创建错误（常用于仅导数据）；rows=n 仅导结构、rows=y 含数据。

• SQLPlus SPOOL 导出 CSV 与 SQLLoader 导入

  • SPOOL 导出为 CSV（示例脚本 export_csv.sql）

    SET ECHO OFF
    SET TERMOUT OFF
    SET FEEDBACK OFF
    SET HEADING OFF
    SET PAGESIZE 0
    SET LINESIZE 2000
    SET TRIMSPOOL ON
    SET TRIMOUT ON
    SET COLSEP ','
    
    SPOOL /opt/oracle/export/emp.csv
    SELECT empno || ',' || ename || ',' || to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') || ',' || sal
    FROM emp;
    SPOOL OFF
    
    EXIT
    

    • 执行：sqlplus -s scott/tiger@orcl @/opt/oracle/scripts/export_csv.sql
  • SQL*Loader 导入（示例控制文件 emp.ctl）

    LOAD DATA
    INFILE '/opt/oracle/export/emp.csv'
    TRUNCATE
    INTO TABLE emp
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (EMPNO, ENAME, HIREDATE DATE 'yyyy-mm-dd hh24:mi:ss', SAL)
    

    • 执行：sqlldr scott/tiger@orcl CONTROL=/opt/oracle/scripts/emp.ctl LOG=/opt/oracle/load/emp.log BAD=/opt/oracle/load/emp.bad
  • 建议：SPOOL 时用 SELECT … || ‘,’ || … 手工拼接分隔符，较 SET COLSEP 更稳定；LINESIZE 需足够大以避免截断。

• SQL*Plus 执行 SQL 脚本

  • 示例：sqlplus scott/tiger@orcl @/opt/oracle/scripts/init_schema.sql
  • 适用：执行 DDL（建表、索引、约束）与 DML（批量插入脚本）；大文件建议在非高峰时段运行。

三 自动化与运维建议

• 备份策略
  • 逻辑备份（expdp/impdp）便于迁移与部分恢复；物理备份建议配合 RMAN 做全库/增量/归档日志备份，用于灾难恢复与快速回滚。生产库建议开启 ARCHIVELOG 模式。
• 定时与保留
  • 使用 crontab 调度脚本，按日全备、按小时/日增备，保留最近7天或更长；示例：
    • 0 2 * * * /opt/oracle/scripts/dp_full.sh >> /var/log/dp_full.log 2>&1
• 异地与加密
  • 用 rsync/scp 将 DMP/CSV 传至远程存储；对敏感备份使用 GPG 加密，并限制文件权限（如 600）。
• 校验与演练
  • 定期在测试环境还原演练，校验 DMP/脚本/控制文件可用性，确保真正故障时可恢复。

四 常见问题与排查

• 权限与目录
  • expdp/impdp 报目录不存在或无权限：确认 DIRECTORY 对象已创建且用户具备 READ/WRITE，且文件在数据库服务器路径下。
• 字符集与换行
  • 导出 CSV 出现乱码或换行破坏：统一 UTF-8；SPOOL 时设置合适的 LINESIZE/TRIMSPOOL，优先使用拼接方式生成定长/定界文本，减少 SET COLSEP 的不确定因素。
• 大对象与性能
  • 大数据量导出慢：使用 expdp 并行（PARALLEL） 与 COMPRESSION；导入时结合 TABLE_EXISTS_ACTION 与并行度，避免逐行提交。
• 版本与工具选择
  • exp/imp 仅用于兼容老库；新环境优先 expdp/impdp。跨平台迁移或结构/数据分离需求时，优先逻辑导出（Data Pump）。