ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 如何获取Data Pump dump file文件

如何获取Data Pump dump file文件

原创 Oracle 作者：xfhuangfu 时间：2026-08-12 16:35:28 0 删除 编辑

适用范围
适用于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=allExport: Release 19.0.0.0.0 - Production on Mon Jul 06 18:08:40 2026Version 19.27.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production06-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=all006-JUL-26 18:12:33.550: Master table "C##BACKUPUSER"."SYS_EXPORT_FULL_01" successfully loaded/unloaded06-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.dmp06-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 2026Version 19.27.0.0.0Copyright (c) 1982, 2024, Oracle.  All rights reserved.
Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.27.0.0.0Hello------------------------------------------------Welcome! you are connected to CDB19C databaseCONNAME--------------------------------------------------------------------------------CDB$ROOTSYS@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 NOSYS@cdb19c(CDB$ROOT)>--创建存储过程 show_dumpfile_infoSYS@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-2026Required.: 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=100300Import: Release 19.0.0.0.0 - Production on Fri Jul 16 00:26:36 2026Version 19.27.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "C##BACKUPUSER"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloadedStarting "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=100300Processing object type TABLE_EXPORT/TABLE/TABLEProcessing 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.trcTrace file /u01/app/oracle/diag/rdbms/cdb19c/cdb19c/trace/cdb19c_dm00_19273.trcOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.27.0.0.0Build label:    RDBMS_19.27.0.0.0DBRU_LINUX.X64_250331
ORACLE_HOME:    /u01/app/oracle/product/19c/db_1System name:    Linux
Node name:      19cdb01Release:        4.14.35-1818.3.3.el7uek.x86_64Version:        #2 SMP Mon Sep 24 14:45:01 PDT 2018Machine:        x86_64
CLID:   PInstance name: cdb19cInstance number: 1Database name: cdb19cDatabase unique name: cdb19cDatabase id: 559927436Database role: PRIMARYRedo thread mounted by this instance: 1Oracle process number: 69Unix 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:00KUPF: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:00KUPF:00:29:15.083: In INIT_CB
KUPF:00:29:15.084: dump file block size:   4096KUPF:00:29:15.084: metadata buffer size:   131072KUPF:00:29:15.084: table data buffer size: 262144KUPF:00:29:15.084: min phy block size:     512KUPF:00:29:15.084: max phy block size:     32768KUPF:00:29:15.084: metadata compression:   0KUPF:00:29:15.084: tabldata compression:   0KUPF:00:29:15.084: metadata encryption:    0KUPF:00:29:15.084: tabldata encryption:    0KUPF:00:29:15.084: column encryption:      0KUPF:00:29:15.084: compression algorithm:  NONEKUPF:00:29:15.084: job version:            19.00.00.00.00KUPF: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  = 1KUPF:00:29:15.120: In ADD_FILE: fileSize  = 0KUPF:00:29:15.120: In ADD_FILE: reuseFile =
KUPF:00:29:15.120: In ADD_FILE: is_dba    = TRUEKUPF:00:29:15.120: In ADD_FILE: read_only = TRUEKUPF: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  = FALSEKUPF:00:29:15.120: is_dba    = TRUEKUPF:00:29:15.120: read_only = TRUEKUPF:00:29:15.120: fileName  = hrpdb_full_20260706.dmp
KUPF:00:29:15.120: directory = IMP_DIR
KUPF:00:29:15.120: filetype  = 1KUPF:00:29:15.120: trans_env = TRUEKUPF: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 FALSEKUPF: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.00KUPF:00:29:15.219: File Version Str = 5.1KUPF:00:29:15.219: File Version Num = 1281KUPF:00:29:15.219: Version CapBits1 = 100351KUPF:00:29:15.219: ......Has Master = 1KUPF:00:29:15.219: ........Job Guid = 548574160F2C6EA3E065DDCE3A0EF6FC
KUPF:00:29:15.219: Master Table Pos = 8KUPF:00:29:15.219: Master Table Len = 174024KUPF:00:29:15.219: Master Table Fsi = 001:001:000001:000001KUPF:00:29:15.219: .....File Number = 1KUPF:00:29:15.219: ......Charset ID = 873KUPF:00:29:15.219: ...Creation date = Thu Jun 18 18:15:33 2026KUPF:00:29:15.219: ...........Flags = 514KUPF:00:29:15.219: ......Media Type = 0KUPF: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 = 4096KUPF: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 0ksvp2penabled: ep->flg = 0, rpr->slv_flg = 0ksvp2penabled: ep = 0x7f1cb7ed3880, rpr = 0x7bb30810

impdp时开启了trace，cdb19c_dm00_19273.trc中输出了hrpdb_full_20260706.dmp的信息。
-the end-


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28373936/viewspace-3179619/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 19c CDB架构下使用具有SYSBACKUP权限用户执行DataPump-expdp报ORA-01045和ORA-39406
下一篇： Oracle 19c CDB中如何创建PDB
请登录后发表评论 登录
全部评论
xfhuangfu
专家姓名：皇甫晓飞
Xiaofei Huangfu Oracle DBA，Oracle ACE Oracle 19c OCM12c OCM,11g OCM MySQL 5.6,5.7,8.0 OCP 18年以上IT从业经验，有丰富的数据库和开发领域经验，多年Oracle运维经验和java开发经验。

注册时间：2013-09-05

  • 博文量
    151
  • 访问量
    231216

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593