Oracle 19c中业务表的列发生变化时使用impdp
适用范围
适用于Oracle 19c
方案概述
生产环境中有需要对业务表的列进行修改，主要是表字段的增加和删除。变更前通过expdp将表导出，字段变更后使用impdp将表数据导入。
当目标表的结构（列的增加、删除、默认值等DDL操作）与原导出的 DMP 文件的列不一致时，使用 TABLE_EXISTS_ACTION=TRUNCATE 参数依然可以成功导入数据。
实施步骤
1、准备工作-创建用户
登录到pdb中创建执行数据泵操作的用户
--登录到CDB
[oracle@19cdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 19 14:35:37 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
Hello
------------------------------------------------
Welcome! you are connected to CDB19C database
CONNAME
--------------------------------------------------------------------------------
CDB$ROOT
SYS@cdb19c(CDB$ROOT)> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HRPDB READ WRITE NO
6 RCATPDB READ WRITE NO
--切换到pdb
SYS@cdb19c(CDB$ROOT)> alter session set container=HRPDB;
Session altered.
--在HRPDB中创建执行数据泵（expdp/impdp）的用户并授权
SYS@cdb19c(CDB$ROOT)> create user expoer identified by Oracle_2026;
User created.
SYS@cdb19c(CDB$ROOT)> grant connect,resource to expoer;
Grant succeeded.
SYS@cdb19c(CDB$ROOT)> grant EXP_FULL_DATABASE to expoer;
Grant succeeded.
SYS@cdb19c(CDB$ROOT)> grant IMP_FULL_DATABASE to expoer;
Grant succeeded.
--directory IMP_DIR已经提前创建，如果没有创建需要创建directory并保证空间足够，权限正确
SYS@cdb19c(CDB$ROOT)> grant write,read on directory IMP_DIR to expoer;
Grant succeeded.
--修改用户的表空间配额
SYS@cdb19c(CDB$ROOT)> ALTER USER expoer QUOTA unlimited ON users;
User altered.
SYS@cdb19c(CDB$ROOT)>2、创建业务表
登录hr业务用户
[oracle@19cdb01 ~]$ sqlplus hr@hrpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 19 14:41:11 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Tue Jun 24 2025 03:50:35 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
--创建表
HR@hrpdb(HRPDB)> create table emp2 as select * from employees;
Table created.
--验证表
HR@hrpdb(HRPDB)> select count(*) from emp2;
COUNT(*)
----------
107
HR@hrpdb(HRPDB)> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@hrpdb(HRPDB)>3、执行empdp对emp2 导出
expdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 logfile=exp_emp2_20260604.log
[oracle@19cdb01 ~]$ expdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 logfile=exp_emp2_20260604.log
Export: Release 19.0.0.0.0 - Production on Thu Jun 04 10:31:38 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "EXPOER"."SYS_EXPORT_TABLE_01": expoer/********@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 logfile=exp_emp2_20260604.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."EMP2" 13.75 KB 107 rows
Master table "EXPOER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPOER.SYS_EXPORT_TABLE_01 is:
/home/oracle/emp2_20260604.dmp
Job "EXPOER"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jun 04 10:31:55 2026 elapsed 0 00:00:154、修改字段进行impdp
场景1：
对emp2表进行字段修改-删除一个字段
HR@hrpdb(HRPDB)> ALTER TABLE emp2 drop COLUMN EMAIL;
Table altered.
HR@hrpdb(HRPDB)> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@hrpdb(HRPDB)>使用impdp导入emp2
使用了参数TABLE_EXISTS_ACTION= TRUNCATE
[oracle@19cdb01 ~]$ impdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION= TRUNCATE logfile=impdp_emp2_20260604.log
Import: Release 19.0.0.0.0 - Production on Thu Jun 04 10:34:05 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "EXPOER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EXPOER"."SYS_IMPORT_TABLE_01": expoer/********@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION=TRUNCATE logfile=impdp_emp2_20260604.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "HR"."EMP2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP2" 13.75 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "EXPOER"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 04 10:34:12 2026 elapsed 0 00:00:06成功impdp。
场景2：
对emp2表进行字段修改-增加一个字段
HR@hrpdb(HRPDB)> ALTER TABLE emp2 add (address varchar2(30));
Table altered.
HR@hrpdb(HRPDB)> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
ADDRESS VARCHAR2(30)
HR@hrpdb(HRPDB)>执行impdp对emp2进行导入（使用了参数TABLE_EXISTS_ACTION= TRUNCATE）
impdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION= TRUNCATE logfile=impdp_emp2_202606042.log
[oracle@19cdb01 ~]$ impdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION= TRUNCATE logfile=impdp_emp2_202606042.log
Import: Release 19.0.0.0.0 - Production on Thu Jun 04 10:40:06 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "EXPOER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EXPOER"."SYS_IMPORT_TABLE_01": expoer/********@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION=TRUNCATE logfile=impdp_emp2_202606042.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "HR"."EMP2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP2" 13.75 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "EXPOER"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 04 10:40:13 2026 elapsed 0 00:00:06impdp 执行成功。
场景3：
对emp2表进行字段修改-增加一个字段有默认值
HR@hrpdb(HRPDB)> alter table emp2 add (userName varchar2(30) default 'HFXF' not null);
Table altered.
HR@hrpdb(HRPDB)> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
ADDRESS VARCHAR2(30)
USERNAME NOT NULL VARCHAR2(30)
HR@hrpdb(HRPDB)>执行impdp对emp2进行导入（使用了参数TABLE_EXISTS_ACTION= TRUNCATE）
[oracle@19cdb01 ~]$ impdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION= TRUNCATE logfile=impdp_emp2_202606043.log
Import: Release 19.0.0.0.0 - Production on Thu Jun 04 10:42:50 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "EXPOER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EXPOER"."SYS_IMPORT_TABLE_01": expoer/********@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION=TRUNCATE logfile=impdp_emp2_202606043.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "HR"."EMP2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP2" 13.75 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "EXPOER"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 04 10:42:54 2026 elapsed 0 00:00:03impdp执行成功。
场景4：
对emp2表进行字段修改-增加3个字段
HR@hrpdb(HRPDB)> ALTER TABLE emp2 add (address4 varchar2(30));
Table altered.
HR@hrpdb(HRPDB)> ALTER TABLE emp2 add (address5 varchar2(30));
Table altered.
HR@hrpdb(HRPDB)> ALTER TABLE emp2 add (address6 varchar2(30));
Table altered.执行impdp对emp2进行导入（使用了参数TABLE_EXISTS_ACTION= TRUNCATE）
[oracle@19cdb01 ~]$ impdp expoer/Oracle_123@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION= TRUNCATE logfile=impdp_emp2_202606043.log
Import: Release 19.0.0.0.0 - Production on Thu Jun 04 10:42:50 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "EXPOER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EXPOER"."SYS_IMPORT_TABLE_01": expoer/********@hrpdb directory=IMP_DIR dumpfile=emp2_20260604.dmp tables=hr.emp2 TABLE_EXISTS_ACTION=TRUNCATE logfile=impdp_emp2_202606043.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "HR"."EMP2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP2" 13.75 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "EXPOER"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 04 10:42:54 2026 elapsed 0 00:00:03
[oracle@19cdb01 ~]$impdp执行成功。
场景编号 变更操作 (DDL) 目标表与 DMP 文件差异 impdp 执行结果 数据加载行为
——- —————— ———————– ———— ————-
场景 1 ALTER TABLE emp2 DROP COLUMN EMAIL; 目标表少一个字段（EMAIL） 成功 自动忽略 DMP 中多余的 EMAIL 列数据，其余字段正常装载 。
场景 2 ALTER TABLE emp2 ADD (address varchar2(30)); 目标表多一个允许为空的字段（ADDRESS） 成功 正常加载历史数据，新增加的 ADDRESS 列在历史数据中自动留空（NULL） 。
场景 3 ALTER TABLE emp2 ADD (userName varchar2(30) DEFAULT ‘HFXF’ NOT NULL); 目标表多一个带默认值的非空字段（USERNAME） 成功 正常加载历史数据，新增加的 USERNAME 列自动应用默认值 ‘HFXF’ 填充 。
场景 4 ALTER TABLE emp2 ADD (address4… address5… address6…); 目标表多个字段 成功 正常加载历史数据，多个新列自动留空 。
【小结】在Oracle 19c中业务表修改字段通过expdp和impdp可以成功完成变更。在Oracle 10g中可能会因为字段发生变化impdp失败。对于业务表修改字段的变更在19c中虽然通过empdp和impdp可以实现，但是我们建议针对此类变更操作可以先通过CTAS创建表，然后修改字段，最后重名表。也可以按目标要求创建好表在进行表级的数据迁移。虽然 19c 提供了这种特性，但在生产环境中针对高并发、大量数据的核心业务表进行字段变更时，仍不建议盲目依赖此特性来变更。
当我们在生产环境中频繁面对表结构变更和数据迁移时，仅仅依赖 TABLE_EXISTS_ACTION=TRUNCATE 虽然能成功，但在性能、安全和高可用上还有很大的优化空间。
如果是线下测试，TABLE_EXISTS_ACTION=TRUNCATE 配合 PARALLEL 和 EXCLUDE=STATISTICS 是最快最省事的。但如果是生产环境核心大表，请优先考虑 DBMS_REDEFINITION 在线重定义 ，先建表后迁移数据（CONTENT=DATA_ONLY）的方案。
-the end –