**适用范围**

适用于Oracle 19c

**方案概述**

生产环境中有需要对业务表的列进行修改，主要是表字段的增加和删除。变更前通过expdp将表导出，字段变更后使用impdp将表数据导入。

当目标表的结构（列的增加、删除、默认值等）与原导出的 DMP 文件不一致时，使用 TABLE_EXISTS_ACTION=TRUNCATE 参数依然可以成功导入数据。

**实施步骤**

**1、准备工作-创建用户**

登录到pdb中创建执行数据泵操作的用户

```sql

--登录到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

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业务用户

```sql

[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

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 导出

```sql

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

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:15

```

**4、修改字段进行impdp

场景1：**

对emp2表进行字段修改-删除一个字段

```sql

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

```sql

[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

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表进行字段修改-增加一个字段

```sql

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）

```sql

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

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:06

```

impdp 执行成功。

**场景3：**

对emp2表进行字段修改-增加一个字段有默认值

```sql

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）

```sql

[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

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

```

impdp执行成功。

**场景4：**

对emp2表进行字段修改-增加3个字段

```sql

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）

```sql

[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

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执行成功。

![image.png](1)

【小结】在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 -

**MyBologs：**

https://www.myhfxf.com

https://www.xiaofeihuangfu.com

CSDN: https://blog.csdn.net/xfhuangfu

ITPUB: https://blog.itpub.net/28373936/

微信公众号：xfhuangfu

ITPub博客

Oracle 19c中业务表的列发生变化时使用impdp

最新文章