如何获取Data Pump dump file文件
适用范围
适用于Oracle 11g及以上版本
方案概述
在生产运维过程中，对于expdp导出的dump file文件处理expdp的logfile之外，通过数据库中创建存储过程或在操作系统上获取dump文件的信息。
实施步骤
1、执行expdp DataPump dumpfile文件
[oracle@19cdb01 ~]$ expdp 'C##BACKUPUSER/<your_password>@hrpdb' directory=IMP_DIR dumpfile=hrpdb_full_20260706.dmp full=y logfile=hrpdb_full_log_20260706.log logtime=all
Export: Release 19.0.0.0.0 - Production on Mon Jul 06 18:08:40 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
06-JUL-26 18:08:45.659: Starting "C##BACKUPUSER"."SYS_EXPORT_FULL_01": C##BACKUPUSER/********@hrpdb directory=IMP_DIR dumpfile=hrpdb_full_20260706.dmp full=y logfile=hrpdb_full_log_20260706.log logtime=all
0
06-JUL-26 18:12:33.550: Master table "C##BACKUPUSER"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
06-JUL-26 18:12:33.583: ******************************************************************************
06-JUL-26 18:12:33.583: Dump file set for C##BACKUPUSER.SYS_EXPORT_FULL_01 is:
06-JUL-26 18:12:33.588: /home/oracle/hrpdb_full_20260706.dmp
06-JUL-26 18:12:33.630: Job "C##BACKUPUSER"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Mon Jul 06 18:12:33 2026 elapsed 0 00:03:52
[oracle@19cdb01 ~]$使用C##BACKUPUSER用户在hrpdb中expdp导出dump file hrpdb_full_20260706.dmp。
2、创建存储过程show_dumpfile_info
登录目标数据库PDB hrpdb
[oracle@19cdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 18 23:56:02 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)> alter session set container=hrpdb;
Session altered.
SYS@cdb19c(CDB$ROOT)> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 HRPDB READ WRITE NO
SYS@cdb19c(CDB$ROOT)>
--创建存储过程 show_dumpfile_info
SYS@cdb19c(CDB$ROOT)> CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
2 3 p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
4 AS
-- p_dir = directory object where dump file can be found
5 6 -- p_file = simple filename of export dump file (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
7 8 9 v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 (etc.)
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(23) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE CONSTANT NUMBER := 22;
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -- For Oracle12cR1:
-- dbms_datapump.KU$_DFHDR_COMPRESSION_ALG CONSTANT NUMBER := 23;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 23;
-- Show header output info:
-- ========================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 16-JUL-2026');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(23);
FOR i in 1 .. 23 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype >= 1 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
dbms_output.put_line('...Export Version................: ' || var_values(15));
ELSIF v_filetype = 1 OR v_filetype = 3 THEN
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
'4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT D 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 ECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Password and Wallet)',
'5', '5 (Wallet)',
var_values(22)) INTO var_values(22) FROM dual;
SELECT DECODE(var_values(23),
'2', '2 (None)',
'3', '3 (Basic)',
'4', '4 (Low)',
'5', '5 (Medium)',
'6', '6 (High)',
var_values(23)) INTO var_values(23) FROM dual;
IF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
END IF;
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...Job Name......................: ' || var_values(8));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 (23));
END IF;
dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
ELSE
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
END IF;
dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
Procedure created.
SYS@cdb19c(CDB$ROOT)>在PDB hrpdb中创建了存储过程show_dumpfile_info。
3、执行存储过程 show_dumpfile_info
SYS@cdb19c(CDB$ROOT)> alter session set container=hrpdb;
Session altered.
SYS@cdb19c(CDB$ROOT)> desc show_dumpfile_info
PROCEDURE show_dumpfile_info
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIR VARCHAR2 IN DEFAULT
P_FILE VARCHAR2 IN DEFAULT
SYS@cdb19c(CDB$ROOT)>
show_dumpfile_info 存储过程需要传入2个参数，dumpfile的directory和dumpfile 的名字。
--执行存储过程
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'IMP_DIR', p_file=> 'hrpdb_full_20260706.dmp')
SYS@cdb19c(CDB$ROOT)> exec show_dumpfile_info(p_dir=> 'IMP_DIR', p_file=> 'hrpdb_full_20260706.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 16-JUL-2026
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: hrpdb_full_20260706.dmp
Directory: IMP_DIR
Disk Path: /home/oracle
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 19.00.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Thu Jun 18 18:15:33 2026
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: cdb19c
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYSBACKUP"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: 548574160F2C6EA3E065DDCE3A0EF6FC
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 25
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
执行存储过程show_dumpfile_info后，输出了dumpfile文件的基本信息。
本案例是用sys用户创建和执行的存储过程，如果需要用户hfxf执行，进行授权。
SYS@cdb19c(CDB$ROOT)> GRANT execute ON show_dumpfile_info to hfxf;
Grant succeeded.
SYS@cdb19c(CDB$ROOT)>
--如果不再使用该存储过程，可以进行删除
SYS@cdb19c(CDB$ROOT)> DROP PROCEDURE show_dumpfile_info;
Procedure dropped.
SYS@cdb19c(CDB$ROOT)>4、使用操作系统命令获取dumpfile 文件信息
[oracle@19cdb01 ~]$ cat hrpdb_full_20260706.dmp | head | strings
"SYSBACKUP"."SYS_EXPORT_TABLE_01"
x86_64/Linux 2.4.xx
cdb19c
AL32UTF8
19.00.00.00.00
001:001:000001:000001
HDR>T<?`
mAr{
d;]!u:~
:l8a
0F`()
(rHE2
k[$#
Up+C
ffffo
i<?xml version="1.0"?><ROWSET><ROW>
<STRMTABLE_T><VERS_MAJOR>1</VERS_MAJOR><VERS_MINOR>2 </VERS_MINOR><VERS_DPAPI>3</VERS_DPAPI><ENDIANNESS>2</ENDIANNESS><CHARSET>AL32UTF8</CHARSET><NCHARSET>AL16UTF16</NCHARSET><DBTIMEZONE>+00:00</DBTIMEZONE><FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B0470083036907D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO><OBJ_NUM>75730</OBJ_NUM><OWNER_NAME>HR</OWNER_NAME><NAME>EMP2</NAME><PROPERTY>537395200</PROPERTY><PROPERTY2>0</PROPERTY2><COL_LIST><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>1</COL_NUM><INTCOL_NUM>1</INTCOL_NUM><SEGCOL_NUM>1</SEGCOL_NUM><COL_SORTKEY>1</COL_SORTKEY><BASE_INTCOL_NUM>1</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>EMPLOYEE_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><PRECISION_NUM>6</PRECISION_NUM><SCALE>0</SCALE><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>2</COL_NUM><INTCOL_NUM>2</INTCOL_NUM><SEGCOL_NUM>2</SEGCOL_NUM><COL_SORTKEY>2</COL_SORTKEY><BASE_INTCOL_NUM>2</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>FIRST_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>20</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>20</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>3</COL_NUM><INTCOL_NUM>3</INTCOL_NUM><SEGCOL_NUM>3</SEGCOL_NUM><COL_SORTKEY>3</COL_SORTKEY><BASE_INTCOL_NUM>3</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>LAST_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>25</LENGTH><NOT_NULL>1</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>25</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>4</COL_NUM><INTCOL_NUM>4</INTCOL_NUM><SEGCOL_NUM>4</SEGCOL_NUM><COL_SORTKEY>4</COL_SORTKEY><BASE_INTCOL_NUM>4</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>PHONE_NUMBER</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>20</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>20</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>5</COL_NUM><INTCOL_NUM>5</INTCOL_NUM><SEGCOL_NUM>5</SEGCOL_NUM><COL_SORTKEY>5</COL_SORTKEY><BASE_INTCOL_NUM>5</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>COMMISSION_PCT</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><PRECISION_NUM>2</PRECISION_NUM><SCALE>2</SCALE><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>6</COL_NUM><INTCOL_NUM>6</INTCOL_NUM><SEGCOL_NUM>6</SEGCOL_NUM><COL_SORTKEY>6</COL_SORTKEY><BASE_INTCOL_NUM>6</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>MANAGER_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><PRECISION_NUM>6</PRECISION_NUM><SCALE>0</SCALE><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>7</COL_NUM><INTCOL_NUM>7</INTCOL_NUM><SEGCOL_NUM>7</SEGCOL_NUM><COL_SORTKEY>7</COL_SORTKEY><BASE_INTCOL_NUM>7</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>DEPARTMENT_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><PRECISION_NUM>4</PRECISION_NUM><SCALE>0</SCALE><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>8</COL_NUM><INTCOL_NUM>9</INTCOL_NUM><SEGCOL_NUM>9</SEGCOL_NUM><COL_SORTKEY>9</COL_SORTKEY><BASE_INTCOL_NUM>9</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>9</COL_NUM><INTCOL_NUM>10</INTCOL_NUM><SEGCOL_NUM>10</SEGCOL_NUM><COL_SORTKEY>10</COL_SORTKEY><BASE_INTCOL_NUM>10</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>1073741824</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>USERNAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>1</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>10</COL_NUM><INTCOL_NUM>13</INTCOL_NUM><SEGCOL_NUM>13</SEGCOL_NUM><COL_SORTKEY>13</COL_SORTKEY><BASE_INTCOL_NUM>13</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS1</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>11</COL_NUM><INTCOL_NUM>14</INTCOL_NUM><SEGCOL_NUM>14</SEGCOL_NUM><COL_SORTKEY>14</COL_SORTKEY><BASE_INTCOL_NUM>14</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS2</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>12</COL_NUM><INTCOL_NUM>15</INTCOL_NUM><SEGCOL_NUM>15</SEGCOL_NUM><COL_SORTKEY>15</COL_SORTKEY><BASE_INTCOL_NUM>15</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS3</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>13</COL_NUM><INTCOL_NUM>16</INTCOL_NUM><SEGCOL_NUM>16</SEGCOL_NUM><COL_SORTKEY>16</COL_SORTKEY><BASE_INTCOL_NUM>16</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS4</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>14</COL_NUM><INTCOL_NUM>17</INTCOL_NUM><SEGCOL_NUM>17</SEGCOL_NUM><COL_SORTKEY>17</COL_SORTKEY><BASE_INTCOL_NUM>17</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS5</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>75730</OBJ_NUM><COL_NUM>15</COL_NUM><INTCOL_NUM>18</INTCOL_NUM><SEGCOL_NUM>18</SEGCOL_NUM><COL_SORTKEY>18</COL_SORTKEY><BASE_INTCOL_NUM>18</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><PROPERTY2>0</PROPERTY2><NAME>ADDRESS6</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>873</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM></COL_LIST></STRMTABLE_T></ROW></ROWSET>
Steven
King
515.123.4567
HFXF<
Neena
...5、通过impdp生成sql文件时trace跟踪
[oracle@19cdb01 ~]$ impdp 'C##BACKUPUSER/Oracle_2026@hrpdb' directory=IMP_DIR dumpfile=hrpdb_full_20260706.dmp NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=hr.emp2 TRACE=100300
--impdp时 使用了参数SQLFILE和TRACE=100300
Import: Release 19.0.0.0.0 - Production on Fri Jul 16 00:26:36 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 "C##BACKUPUSER"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "C##BACKUPUSER"."SYS_SQL_FILE_TABLE_01": C##BACKUPUSER/********@hrpdb directory=IMP_DIR dumpfile=hrpdb_full_20260706.dmp NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=hr.emp2 TRACE=100300
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "C##BACKUPUSER"."SYS_SQL_FILE_TABLE_01" successfully completed at Fri Jul 16 00:26:39 2026 elapsed 0 00:00:02
[oracle@19cdb01 trace]$ more cdb19c_dm00_19273.trc
Trace file /u01/app/oracle/diag/rdbms/cdb19c/cdb19c/trace/cdb19c_dm00_19273.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
Build label: RDBMS_19.27.0.0.0DBRU_LINUX.X64_250331
ORACLE_HOME: /u01/app/oracle/product/19c/db_1
System name: Linux
Node name: 19cdb01
Release: 4.14.35-1818.3.3.el7uek.x86_64
Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018
Machine: x86_64
CLID: P
Instance name: cdb19c
Instance number: 1
Database name: cdb19c
Database unique name: cdb19c
Database id: 559927436
Database role: PRIMARY
Redo thread mounted by this instance: 1
Oracle process number: 69
Unix process pid: 19273, image: oracle@19cdb01 (DM00)
*** 2026-06-19T00:29:14.959145+08:00 (HRPDB(3))
*** SESSION ID:(464.58280) 2026-06-19T00:29:14.959168+08:00
*** CLIENT ID:() 2026-06-19T00:29:14.959171+08:00
*** SERVICE NAME:(hrpdb) 2026-06-19T00:29:14.959174+08:00
*** MODULE NAME:() 2026-06-19T00:29:14.959177+08:00
*** ACTION NAME:() 2026-06-19T00:29:14.959179+08:00
*** CLIENT DRIVER:(SERVER) 2026-06-19T00:29:14.959182+08:00
*** CONTAINER ID:(3) 2026-06-19T00:29:14.959184+08:00
KUPF:00:29:14.958: SET DEBUG called
*** MODULE NAME:(Data Pump Master) 2026-06-19T00:29:15.084003+08:00
*** ACTION NAME:(SYS_SQL_FILE_FULL_01) 2026-06-19T00:29:15.084024+08:00
KUPF:00:29:15.083: In INIT_CB
KUPF:00:29:15.084: dump file block size: 4096
KUPF:00:29:15.084: metadata buffer size: 131072
KUPF:00:29:15.084: table data buffer size: 262144
KUPF:00:29:15.084: min phy block size: 512
KUPF:00:29:15.084: max phy block size: 32768
KUPF:00:29:15.084: metadata compression: 0
KUPF:00:29:15.084: tabldata compression: 0
KUPF:00:29:15.084: metadata encryption: 0
KUPF:00:29:15.084: tabldata encryption: 0
KUPF:00:29:15.084: column encryption: 0
KUPF:00:29:15.084: compression algorithm: NONE
KUPF:00:29:15.084: job version: 19.00.00.00.00
KUPF:00:29:15.084: (kupfxInit) File Manager has been initialized...
*** 2026-06-19T00:29:15.117346+08:00 (HRPDB(3))
KUPF:00:29:15.117: In kupfxIsDmpRestFile...
KUPF:00:29:15.117: In kupfiIsDmpRestFile...
KUPF:00:29:15.117: Leaving kupfiIsDmpRestFile...
KUPF:00:29:15.120: In ADD_FILE: fileName = hrpdb_full_20260706.dmp
KUPF:00:29:15.120: In ADD_FILE: directory = IMP_DIR
KUPF:00:29:15.120: In ADD_FILE: fileType = 1
KUPF:00:29:15.120: In ADD_FILE: fileSize = 0
KUPF:00:29:15.120: In ADD_FILE: reuseFile =
KUPF:00:29:15.120: In ADD_FILE: is_dba = TRUE
KUPF:00:29:15.120: In ADD_FILE: read_only = TRUE
KUPF:00:29:15.120: In ADD_FILE: The file name WITH substitution variables = hrpdb_full_20260706.dmp
KUPF:00:29:15.120: In ADD_FILE: The file name WITHOUT substitution variables = hrpdb_full_20260706.dmp
KUPF:00:29:15.120: In ADD_FILE: Has a wildCard_d = FALSE ,Has a wildCard_m = FALSE ,Has a wildCard_t = FALSE ,Has a wil
dCard_y = FALSE
KUPF:00:29:15.120: is_dba = TRUE
KUPF:00:29:15.120: read_only = TRUE
KUPF:00:29:15.120: fileName = hrpdb_full_20260706.dmp
KUPF:00:29:15.120: directory = IMP_DIR
KUPF:00:29:15.120: filetype = 1
KUPF:00:29:15.120: trans_env = TRUE
KUPF:00:29:15.120: In kupfxParseFileName...
KUPF:00:29:15.160: directory = IMP_DIR
KUPF:00:29:15.161: In kupfxGetDefFileName...
KUPF:00:29:15.218: In kupfxExmDmpFile...
KUPF:00:29:15.218: In kupfuExmDmpFile...
KUPF:00:29:15.218: ...examining dump file /home/oracle/hrpdb_full_20260706.dmp
KUPF:00:29:15.218: In kupfioReadHeader...
KUPF:00:29:15.218: In kupfioFileIsLink...
KUPF:00:29:15.218: kupfioFileIsLink qa FALSE, ddc FALSE
KUPF:00:29:15.218: Leaving kupfioFileIsLink with value FALSE...
KUPF:00:29:15.219: newImpFile: EXAMINE_DUMP_FILE
KUPF:00:29:15.219: ......DB Version = 19.00.00.00.00
KUPF:00:29:15.219: File Version Str = 5.1
KUPF:00:29:15.219: File Version Num = 1281
KUPF:00:29:15.219: Version CapBits1 = 100351
KUPF:00:29:15.219: ......Has Master = 1
KUPF:00:29:15.219: ........Job Guid = 548574160F2C6EA3E065DDCE3A0EF6FC
KUPF:00:29:15.219: Master Table Pos = 8
KUPF:00:29:15.219: Master Table Len = 174024
KUPF:00:29:15.219: Master Table Fsi = 001:001:000001:000001
KUPF:00:29:15.219: .....File Number = 1
KUPF:00:29:15.219: ......Charset ID = 873
KUPF:00:29:15.219: ...Creation date = Thu Jun 18 18:15:33 2026
KUPF:00:29:15.219: ...........Flags = 514
KUPF:00:29:15.219: ......Media Type = 0
KUPF:00:29:15.219: ........Job Name = "SYSBACKUP"."SYS_EXPORT_TABLE_01"
KUPF:00:29:15.219: ........Platform = x86_64/Linux 2.4.xx
KUPF:00:29:15.219: ........Instance = cdb19c
KUPF:00:29:15.219: ........Language = AL32UTF8
KUPF:00:29:15.219: .......Blocksize = 4096
KUPF:00:29:15.223: Added FILE row; PO: -22; FID: 1; NAM: hrpdb_full_20260706.dmp
KUPF:00:29:15.223: newImpFile: file; /home/oracle/hrpdb_full_20260706.dmp, FID; 1
KUPF:00:29:15.225: In kupfxGetDefFileName...
KUPF:00:29:15.225: is_dba = TRUE
KUPF:00:29:15.225: read_only = FALSE
KUPF:00:29:15.225: fileName = impdp_s2.sql
KUPF:00:29:15.225: directory = IMP_DIR
KUPF:00:29:15.225: filetype =
KUPF:00:29:15.225: trans_env = TRUE
KUPF:00:29:15.225: In kupfxParseFileName...
KUPF:00:29:15.258: directory = IMP_DIR
KUPF:00:29:15.258: is_dba = FALSE
KUPF:00:29:15.258: read_only = TRUE
KUPF:00:29:15.258: fileName = impdp_s2.sql
KUPF:00:29:15.258: directory = IMP_DIR
KUPF:00:29:15.258: filetype =
KUPF:00:29:15.258: trans_env = TRUE
KUPF:00:29:15.258: In kupfxParseFileName...
KUPF:00:29:15.290: directory = IMP_DIR
KUPF:00:29:15.290: In kupfxExmDmpFile...
KUPF:00:29:15.290: In kupfuExmDmpFile...
KUPF:00:29:15.290: ...examining dump file /home/oracle/impdp_s2.sql
KUPF:00:29:15.290: In kupfioReadHeader...
KUPF:00:29:15.296: In Locate_Master...
KUPF:00:29:15.297: Master Table Data located: TRUE
KUPF:00:29:15.714: In Verify_DumpFile_Set...
KUPF:00:29:15.715: ..Export GUID: 548574160F2C6EA3E065DDCE3A0EF6FC
KUPF:00:29:15.716: ..Export BlockSize: 4096
*** 2026-06-19T00:29:16.321889+08:00 (HRPDB(3))
KUPF:00:29:16.321: In kupfxTerm...
KUPF:00:29:16.321: File cache 0 entries, hits 0, misses 0
ksvp2penabled: ep->flg = 0, rpr->slv_flg = 0
ksvp2penabled: ep = 0x7f1cb7ed3880, rpr = 0x7bb30810impdp时开启了trace，cdb19c_dm00_19273.trc中输出了hrpdb_full_20260706.dmp的信息。
-the end-
笔者文章集合详见：
https://www.myhfxf.com
https://www.xiaofeihuangfu.com
CSDN:https://blog.csdn.net/xfhuangfu
ITPUB:https://blog.itpub.net/28373936/
微信公众号：xfhuangfu