一、dexp 和 dimp 工具概述
1.1 工具简介
达梦数据库 (DM Database) 提供了一套功能强大的逻辑备份恢复工具: dexp 和 dimp。dexp (DM Export) 用于将数据库中的数据和对象定义导出到操作系统文件中, dimp (DM Import) 则用于将 dexp 导出的文件导入到达梦数据库中。这两个工具类似于 Oracle 数据库的 exp/imp 工具, 是达梦数据库日常运维、数据迁移和版本升级场景中的核心组件, 在实际项目中有着非常广泛的应用。
dexp 和 dimp 的主要特点包括以下几个方面:
1.2 核心参数详解
掌握 dexp 和 dimp 应用实例的关键在于理解其核心参数。dexp 的常用参数如下表所示:
| 参数名称 | 参数说明 | 使用示例 |
| — | — | — |
| USERID | 数据库连接字符串 | SYSDBA/SYSDBA@localhost:5236 |
| FILE | 导出文件名称 | full.dmp |
| DIRECTORY | 导出文件存放目录 | /dm/backup |
| FULL | 是否全库导出 (Y/N) | Y |
| SCHEMAS | 指定模式名称列表 | SCOTT,HR |
| TABLES | 指定表名称列表 | SCOTT.EMP,SCOTT.DEPT |
| ROWS | 是否导出表数据 (Y/N) | Y |
| LOG | 日志文件名称 | exp.log |
| PARALLEL | 并行工作线程数 | 4 |
| QUERY | 按条件导出数据 | "WHERE SAL > 1000" |
对应地, dimp 的核心参数与 dexp 基本一致, 同时额外提供以下导入专用参数:
| 参数名称 | 参数说明 |
| — | — |
| IGNORE | 导入时是否忽略对象创建错误 (Y/N) |
| TABLE_EXISTS_ACTION | 表已存在时的处理动作 (SKIP/APPEND/TRUNCATE/REPLACE) |
| REMAP_SCHEMA | 导入时模式名称重映射 |
| REMAP_TABLE | 导入时表名称重映射 |
| REMAP_TABLESPACE | 导入时表空间重映射 |
1.3 典型应用场景
dexp 和 dimp 应用实例的典型业务场景主要包括以下几类:
以下流程图展示了 dexp 和 dimp 在数据迁移场景中的整体工作流程, 帮助读者建立全局认知:
#publish-mermaid-1786507855837-0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786507855837-0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507855837-0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507855837-0 .error-icon{fill:#552222;}#publish-mermaid-1786507855837-0 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786507855837-0 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786507855837-0 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786507855837-0 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786507855837-0 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786507855837-0 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786507855837-0 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786507855837-0 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786507855837-0 .marker.cross{stroke:#333333;}#publish-mermaid-1786507855837-0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786507855837-0 p{margin:0;}#publish-mermaid-1786507855837-0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786507855837-0 .cluster-label text{fill:#333;}#publish-mermaid-1786507855837-0 .cluster-label span{color:#333;}#publish-mermaid-1786507855837-0 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786507855837-0 .label text,#publish-mermaid-1786507855837-0 span{fill:#333;color:#333;}#publish-mermaid-1786507855837-0 .node rect,#publish-mermaid-1786507855837-0 .node circle,#publish-mermaid-1786507855837-0 .node ellipse,#publish-mermaid-1786507855837-0 .node polygon,#publish-mermaid-1786507855837-0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507855837-0 .rough-node .label text,#publish-mermaid-1786507855837-0 .node .label text,#publish-mermaid-1786507855837-0 .image-shape .label,#publish-mermaid-1786507855837-0 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786507855837-0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786507855837-0 .rough-node .label,#publish-mermaid-1786507855837-0 .node .label,#publish-mermaid-1786507855837-0 .image-shape .label,#publish-mermaid-1786507855837-0 .icon-shape .label{text-align:center;}#publish-mermaid-1786507855837-0 .node.clickable{cursor:pointer;}#publish-mermaid-1786507855837-0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786507855837-0 .arrowheadPath{fill:#333333;}#publish-mermaid-1786507855837-0 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786507855837-0 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786507855837-0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507855837-0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855837-0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855837-0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786507855837-0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786507855837-0 .cluster text{fill:#333;}#publish-mermaid-1786507855837-0 .cluster span{color:#333;}#publish-mermaid-1786507855837-0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786507855837-0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786507855837-0 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786507855837-0 .icon-shape,#publish-mermaid-1786507855837-0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507855837-0 .icon-shape p,#publish-mermaid-1786507855837-0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786507855837-0 .icon-shape .label rect,#publish-mermaid-1786507855837-0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855837-0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786507855837-0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786507855837-0 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node rect,#publish-mermaid-1786507855837-0 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855837-0 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855837-0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
是
否
是
否
是
否
开始数据迁移
分析源库结构
创建目标库与表空间
使用 dexp 导出源库数据
导出是否成功
传输 dmp 文件到目标服务器
排查导出错误
使用 dimp 导入目标库
导入是否成功
校验数据完整性
排查导入错误
数据是否一致
迁移完成
二、dexp 导出实战应用
2.1 环境准备与连接测试
在执行 dexp 和 dimp 应用实例前, 需要完成以下环境准备工作, 确保操作过程顺利:
disql SYSDBA/SYSDBA@localhost:5236
mkdir -p /dm/backup
chown dmdba:dinstall /dm/backup
chmod 755 /dm/backup
which dexp
# 或者直接查看安装目录
ls $DM_HOME/bin/dexp
— 创建测试模式
CREATE SCHEMA SCOTT;
— 创建部门表
CREATE TABLE SCOTT.DEPT (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(50),
LOC VARCHAR(50)
);
— 创建员工表
CREATE TABLE SCOTT.EMP (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50),
JOB VARCHAR(30),
SAL DECIMAL(10,2),
DEPTNO INT
);
— 插入测试数据
INSERT INTO SCOTT.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO SCOTT.DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO SCOTT.DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO SCOTT.EMP VALUES (7369, 'SMITH', 'CLERK', 800, 20);
INSERT INTO SCOTT.EMP VALUES (7499, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO SCOTT.EMP VALUES (7566, 'JONES', 'MANAGER', 2975, 20);
COMMIT;
2.2 全库导出实例
全库导出是 dexp 最基础也是最常用的场景之一, 适用于整体数据备份或全量迁移。执行全库导出的命令格式如下:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=full.dmp \\
DIRECTORY=/dm/backup \\
FULL=Y \\
LOG=full_exp.log \\
PARALLEL=4
各参数含义详细说明如下:
执行完成后, 在 /dm/backup 目录下会生成 full.dmp 数据文件和 full_exp.log 日志文件。通过查看日志尾部可确认导出结果:
tail -50 /dm/backup/full_exp.log
日志中应包含类似如下的关键信息, 表示导出成功完成:
导出模式: FULL
导出表: SCOTT.DEPT
导出表: SCOTT.EMP
…
dexp 成功完成
2.3 按模式导出实例
在实际项目中, 更为常见的场景是按模式 (SCHEMA) 导出, 仅迁移特定业务模式下的对象和数据。按模式导出的命令如下:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=scott.dmp \\
DIRECTORY=/dm/backup \\
SCHEMAS=SCOTT \\
ROWS=Y \\
LOG=scott_exp.log
若需要同时导出多个模式, 在 SCHEMAS 参数后用英文逗号分隔多个模式名即可:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=multi.dmp \\
DIRECTORY=/dm/backup \\
SCHEMAS=SCOTT,HR,SALES \\
ROWS=Y \\
LOG=multi_exp.log
按模式导出时, dexp 的内部处理流程如下图所示, 有助于理解工具的执行机制:
#publish-mermaid-1786507855973-1{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786507855973-1 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507855973-1 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507855973-1 .error-icon{fill:#552222;}#publish-mermaid-1786507855973-1 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786507855973-1 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786507855973-1 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786507855973-1 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786507855973-1 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786507855973-1 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786507855973-1 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786507855973-1 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786507855973-1 .marker.cross{stroke:#333333;}#publish-mermaid-1786507855973-1 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786507855973-1 p{margin:0;}#publish-mermaid-1786507855973-1 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786507855973-1 .cluster-label text{fill:#333;}#publish-mermaid-1786507855973-1 .cluster-label span{color:#333;}#publish-mermaid-1786507855973-1 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786507855973-1 .label text,#publish-mermaid-1786507855973-1 span{fill:#333;color:#333;}#publish-mermaid-1786507855973-1 .node rect,#publish-mermaid-1786507855973-1 .node circle,#publish-mermaid-1786507855973-1 .node ellipse,#publish-mermaid-1786507855973-1 .node polygon,#publish-mermaid-1786507855973-1 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507855973-1 .rough-node .label text,#publish-mermaid-1786507855973-1 .node .label text,#publish-mermaid-1786507855973-1 .image-shape .label,#publish-mermaid-1786507855973-1 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786507855973-1 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786507855973-1 .rough-node .label,#publish-mermaid-1786507855973-1 .node .label,#publish-mermaid-1786507855973-1 .image-shape .label,#publish-mermaid-1786507855973-1 .icon-shape .label{text-align:center;}#publish-mermaid-1786507855973-1 .node.clickable{cursor:pointer;}#publish-mermaid-1786507855973-1 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786507855973-1 .arrowheadPath{fill:#333333;}#publish-mermaid-1786507855973-1 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786507855973-1 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786507855973-1 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507855973-1 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855973-1 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855973-1 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786507855973-1 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786507855973-1 .cluster text{fill:#333;}#publish-mermaid-1786507855973-1 .cluster span{color:#333;}#publish-mermaid-1786507855973-1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786507855973-1 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786507855973-1 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786507855973-1 .icon-shape,#publish-mermaid-1786507855973-1 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507855973-1 .icon-shape p,#publish-mermaid-1786507855973-1 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786507855973-1 .icon-shape .label rect,#publish-mermaid-1786507855973-1 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507855973-1 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786507855973-1 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786507855973-1 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node rect,#publish-mermaid-1786507855973-1 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855973-1 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507855973-1 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
是
否
是
否
dexp 启动
连接数据库
解析 SCHEMAS 参数
遍历每个模式
导出模式定义
导出表结构
ROWS=Y?
导出表数据
跳过数据导出
导出索引和约束
导出触发器和视图
是否还有模式
写入 dmp 文件
输出日志并退出
2.4 按表导出实例
按表导出适用于精细化的数据迁移场景, 仅导出指定的单张或多张表。基本命令格式如下:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=tables.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP,SCOTT.DEPT \\
ROWS=Y \\
LOG=tables_exp.log
若仅需导出表结构 (DDL) 而不导出数据, 将 ROWS 参数设置为 N 即可:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=ddl_only.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP \\
ROWS=N \\
LOG=ddl_exp.log
此外, 通过 QUERY 参数可实现条件导出, 仅导出满足特定条件的数据行, 在数据归档和抽样迁移场景中非常实用:
dexp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=cond.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP \\
ROWS=Y \\
QUERY="WHERE SAL > 1000" \\
LOG=cond_exp.log
上述命令仅导出 SCOTT.EMP 表中工资大于 1000 的员工记录, 大大减少了导出文件体积和传输时间。
三、dimp 导入实战应用
3.1 导入前准备工作
使用 dimp 执行数据导入前, 需要做好以下准备工作, 确保导入过程顺利:
disql SYSDBA/SYSDBA@target_host:5236
SELECT * FROM V$VERSION;
— 创建表空间
CREATE TABLESPACE SCOTT_DATA
DATAFILE '/dm/data/SCOTT_DATA.dbf' SIZE 128 AUTOEXTEND ON;
— 创建用户, 模式会随用户自动创建
CREATE USER SCOTT IDENTIFIED BY "SCOTT123"
DEFAULT TABLESPACE SCOTT_DATA;
GRANT RESOURCE, PUBLIC TO SCOTT;
scp /dm/backup/full.dmp dmdba@target_host:/dm/backup/
df -h /dm
3.2 全库导入实例
全库导入对应于全库导出生成的 dmp 文件, 命令格式如下:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/full.dmp \\
DIRECTORY=/dm/backup \\
FULL=Y \\
LOG=full_imp.log \\
PARALLEL=4
若目标库中已存在同名对象, 可通过 TABLE_EXISTS_ACTION 参数控制处理策略:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/full.dmp \\
DIRECTORY=/dm/backup \\
FULL=Y \\
TABLE_EXISTS_ACTION=REPLACE \\
LOG=full_imp.log
TABLE_EXISTS_ACTION 各选项的具体含义如下:
3.3 按模式导入实例
按模式导入是实际项目中最常用的数据迁移方式, 命令格式如下:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/scott.dmp \\
DIRECTORY=/dm/backup \\
SCHEMAS=SCOTT \\
LOG=scott_imp.log
若需要将数据导入到不同名称的模式中, 可使用 REMAP_SCHEMA 参数实现模式重映射:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/scott.dmp \\
DIRECTORY=/dm/backup \\
REMAP_SCHEMA=SCOTT:SCOTT_NEW \\
LOG=remap_imp.log
上述命令将原 SCOTT 模式的全部对象和数据导入到 SCOTT_NEW 模式中, 非常适合环境克隆和数据复制场景。
按模式导入的完整执行流程如下图所示, 展示了 dimp 工具从读取文件到完成导入的全过程:
#publish-mermaid-1786507856079-2{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786507856079-2 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507856079-2 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507856079-2 .error-icon{fill:#552222;}#publish-mermaid-1786507856079-2 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786507856079-2 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786507856079-2 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786507856079-2 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786507856079-2 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786507856079-2 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786507856079-2 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786507856079-2 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786507856079-2 .marker.cross{stroke:#333333;}#publish-mermaid-1786507856079-2 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786507856079-2 p{margin:0;}#publish-mermaid-1786507856079-2 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786507856079-2 .cluster-label text{fill:#333;}#publish-mermaid-1786507856079-2 .cluster-label span{color:#333;}#publish-mermaid-1786507856079-2 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786507856079-2 .label text,#publish-mermaid-1786507856079-2 span{fill:#333;color:#333;}#publish-mermaid-1786507856079-2 .node rect,#publish-mermaid-1786507856079-2 .node circle,#publish-mermaid-1786507856079-2 .node ellipse,#publish-mermaid-1786507856079-2 .node polygon,#publish-mermaid-1786507856079-2 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507856079-2 .rough-node .label text,#publish-mermaid-1786507856079-2 .node .label text,#publish-mermaid-1786507856079-2 .image-shape .label,#publish-mermaid-1786507856079-2 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786507856079-2 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786507856079-2 .rough-node .label,#publish-mermaid-1786507856079-2 .node .label,#publish-mermaid-1786507856079-2 .image-shape .label,#publish-mermaid-1786507856079-2 .icon-shape .label{text-align:center;}#publish-mermaid-1786507856079-2 .node.clickable{cursor:pointer;}#publish-mermaid-1786507856079-2 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786507856079-2 .arrowheadPath{fill:#333333;}#publish-mermaid-1786507856079-2 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786507856079-2 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786507856079-2 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507856079-2 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856079-2 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856079-2 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786507856079-2 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786507856079-2 .cluster text{fill:#333;}#publish-mermaid-1786507856079-2 .cluster span{color:#333;}#publish-mermaid-1786507856079-2 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786507856079-2 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786507856079-2 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786507856079-2 .icon-shape,#publish-mermaid-1786507856079-2 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507856079-2 .icon-shape p,#publish-mermaid-1786507856079-2 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786507856079-2 .icon-shape .label rect,#publish-mermaid-1786507856079-2 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856079-2 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786507856079-2 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786507856079-2 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node rect,#publish-mermaid-1786507856079-2 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856079-2 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856079-2 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
否
是
是
否
dimp 启动
读取 dmp 文件头
校验文件版本与兼容性
文件是否有效
报错并退出
解析导入参数
创建或校验目标模式
导入表结构定义
表是否已存在
按 TABLE_EXISTS_ACTION 处理
直接创建表
导入表数据
导入索引和约束
导入触发器和视图
编译失效对象
输出导入统计日志
导入完成
3.4 按表导入实例
按表导入适用于单表或多表的精细化数据迁移场景, 命令格式如下:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/tables.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP,SCOTT.DEPT \\
LOG=tables_imp.log
若目标表已存在且仅需在已有数据基础上追加新数据, 可配合 APPEND 和 IGNORE 参数使用:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/tables.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP \\
TABLE_EXISTS_ACTION=APPEND \\
IGNORE=Y \\
LOG=append_imp.log
通过 REMAP_TABLE 参数可实现表名重映射, 将源表数据导入到不同名称的目标表中:
dimp USERID=SYSDBA/SYSDBA@localhost:5236 \\
FILE=/dm/backup/tables.dmp \\
DIRECTORY=/dm/backup \\
TABLES=SCOTT.EMP \\
REMAP_TABLE=SCOTT.EMP:SCOTT.EMP_BAK \\
LOG=remap_table_imp.log
上述命令将 SCOTT.EMP 表的数据导入到 SCOTT.EMP_BAK 表中, 适用于数据备份和表级复制场景。
四、进阶技巧与问题排查
4.1 跨平台迁移方案
dexp 和 dimp 应用实例在跨平台迁移 (如 Linux 与 Windows 之间互迁) 时, 需要特别注意以下事项:
# 使用 scp 传输, 默认为二进制模式, 安全可靠
scp full.dmp user@windows_host:D:/backup/
# 若使用 ftp 传输, 需手动切换为二进制模式
ftp> binary
ftp> put full.dmp
# Windows 环境下执行 dimp
dimp USERID=SYSDBA/SYSDBA@localhost:5236 FILE=D:\\\\backup\\\\full.dmp FULL=Y LOG=D:\\\\backup\\\\imp.log
— 查询数据库字符集
SELECT SF_GET_PARA_VALUE(1, 'CHARSET');
4.2 字符集与编码处理
字符集问题是 dexp 和 dimp 应用实例中最常见且最令人困扰的问题之一。完整的字符集问题排查与处理流程如下图所示:
#publish-mermaid-1786507856190-3{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786507856190-3 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507856190-3 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786507856190-3 .error-icon{fill:#552222;}#publish-mermaid-1786507856190-3 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786507856190-3 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786507856190-3 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786507856190-3 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786507856190-3 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786507856190-3 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786507856190-3 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786507856190-3 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786507856190-3 .marker.cross{stroke:#333333;}#publish-mermaid-1786507856190-3 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786507856190-3 p{margin:0;}#publish-mermaid-1786507856190-3 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786507856190-3 .cluster-label text{fill:#333;}#publish-mermaid-1786507856190-3 .cluster-label span{color:#333;}#publish-mermaid-1786507856190-3 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786507856190-3 .label text,#publish-mermaid-1786507856190-3 span{fill:#333;color:#333;}#publish-mermaid-1786507856190-3 .node rect,#publish-mermaid-1786507856190-3 .node circle,#publish-mermaid-1786507856190-3 .node ellipse,#publish-mermaid-1786507856190-3 .node polygon,#publish-mermaid-1786507856190-3 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507856190-3 .rough-node .label text,#publish-mermaid-1786507856190-3 .node .label text,#publish-mermaid-1786507856190-3 .image-shape .label,#publish-mermaid-1786507856190-3 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786507856190-3 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786507856190-3 .rough-node .label,#publish-mermaid-1786507856190-3 .node .label,#publish-mermaid-1786507856190-3 .image-shape .label,#publish-mermaid-1786507856190-3 .icon-shape .label{text-align:center;}#publish-mermaid-1786507856190-3 .node.clickable{cursor:pointer;}#publish-mermaid-1786507856190-3 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786507856190-3 .arrowheadPath{fill:#333333;}#publish-mermaid-1786507856190-3 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786507856190-3 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786507856190-3 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507856190-3 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856190-3 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856190-3 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786507856190-3 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786507856190-3 .cluster text{fill:#333;}#publish-mermaid-1786507856190-3 .cluster span{color:#333;}#publish-mermaid-1786507856190-3 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786507856190-3 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786507856190-3 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786507856190-3 .icon-shape,#publish-mermaid-1786507856190-3 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786507856190-3 .icon-shape p,#publish-mermaid-1786507856190-3 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786507856190-3 .icon-shape .label rect,#publish-mermaid-1786507856190-3 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786507856190-3 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786507856190-3 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786507856190-3 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node rect,#publish-mermaid-1786507856190-3 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856190-3 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786507856190-3 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
是
否
是
否
字符集问题排查
查询源库字符集
查询目标库字符集
字符集是否一致
正常执行导入导出
设置环境变量
源端导出时指定字符集参数
目标端导入时指定字符集参数
验证数据正确性
数据是否乱码
迁移完成
关键操作步骤如下:
SELECT SF_GET_PARA_VALUE(1, 'CHARSET');
# Linux 环境设置
export LANG=zh_CN.UTF-8
export DM_NLS_CHARSET=1
# Windows 环境设置
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.UTF8
dexp USERID=SYSDBA/SYSDBA@localhost:5236 FILE=scott.dmp DIRECTORY=/dm/backup SCHEMAS=SCOTT UTF8=Y LOG=exp.log
4.3 常见错误排查
以下是 dexp 和 dimp 应用实例中常见的错误及其解决方案, 供读者在实际操作中参考:
- 原因分析: 数据库服务未启动、端口配置错误、防火墙拦截网络连接。
- 解决方案: 检查数据库服务运行状态, 确认端口开放, 验证 USERID 连接字符串格式是否正确。
- 原因分析: 执行导出导入操作的数据库用户缺少相应权限。
- 解决方案: 全库导出需 SYSDBA 权限, 模式导出需该模式属主权限或 DBA 权限。
— 授予导出数据库权限
GRANT EXPORT DATABASE TO SCOTT;
- 原因分析: 导入数据量超出目标表空间的可用容量。
- 解决方案: 扩展表空间或开启数据文件自动扩展功能。
ALTER TABLESPACE SCOTT_DATA ADD DATAFILE '/dm/data/SCOTT_DATA2.dbf' SIZE 256 AUTOEXTEND ON;
- 原因分析: 源端与目标端字符集不一致, 导入时未正确设置字符集环境变量。
- 解决方案: 参照 4.2 节内容设置正确的字符集环境变量, 重新执行导入操作。
- 原因分析: 高版本数据库导出的 dmp 文件无法导入低版本数据库。
- 解决方案: 使用兼容版本重新导出, 或将目标数据库升级至兼容版本。
- 原因分析: 目标库中已存在同名对象, 默认 SKIP 策略下跳过但有报错提示。
- 解决方案: 使用 TABLE_EXISTS_ACTION=REPLACE 覆盖已有对象, 或使用 IGNORE=Y 忽略创建错误。
通过系统掌握上述 dexp 和 dimp 应用实例, 读者可以高效完成达梦数据库的逻辑备份、恢复与数据迁移工作。在实际项目实践中, 建议先在测试环境完整验证导入导出流程, 确认数据一致性和业务功能无误后, 再在生产环境正式执行。同时务必保留完整的操作日志文件, 以便后续审计追溯和问题排查。
