Oracle Data Pump - Get Dump File Details

Olá, nesse artigo iremos abordar os passos necessários para listar os detalhes de um DUMP FILE existente, gerado com o expdp via DATA PUMP.

Links úteis

DBMS_DATAPUMP.GET_DUMPFILE_INFO

Vamos gerar um arquivo de dump simples:

1 - Criar o diretório no sistema operacional:

mkdir -p /backup/DATA_PUMP

2 - Listar os diretórios atuais no banco de dados:

export ORACLE_PDB_SID=PDBHERO

sqlplus / AS sysdba

SET LINES 200 pages 2000
col owner FOR a30
col directory_name FOR a30
col directory_path FOR a90
SELECT * FROM dba_directories;

3 - Criar novo diretório no banco de dados:

CREATE OR REPLACE DIRECTORY DP_DIR AS '/backup/DATA_PUMP';

4 - Criar uma tabela de teste e inserir alguns registros:

CREATE TABLE HERO.TESTE (id NUMBER, nome varchar2(50));

INSERT INTO HERO.TESTE (id, nome) VALUES (1, 'HERO');
INSERT INTO HERO.TESTE (id, nome) VALUES (2, 'DBAHERO');
commit;

5 - Realizar o export do schema:

expdp \"/ as sysdba\" SCHEMAS=HERO DIRECTORY=DP_DIR DUMPFILE=expdp_HERO_20250421_%U.dmp LOGFILE=expdp_HERO_20250421.log

Abaixo o log da execução do export:

[oracle@oracledb19c DATA_PUMP]$ expdp \"/ as sysdba\" SCHEMAS=HERO DIRECTORY=DP_DIR DUMPFILE=expdp_HERO_20250421_%U.dmp LOGFILE=expdp_HERO_20250421.log
 
Export: Release 19.0.0.0.0 - Production on Mon Apr 21 16:38:28 2025
Version 19.25.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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" SCHEMAS=HERO DIRECTORY=DP_DIR DUMPFILE=expdp_HERO_20250421_%U.dmp LOGFILE=expdp_HERO_20250421.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HERO"."TESTE"                              5.492 KB       2 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /backup/DATA_PUMP/expdp_HERO_20250421_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 21 16:39:32 2025 elapsed 0 00:00:56

Dump File gerado: /backup/DATA_PUMP/expdp_HERO_20250421_01.dmp

7 - Vamos listar os metadados desse dump file:

sqlplus / as sysdba

show_dumpfile_details.sql

SET LINES 200 PAGES 2000
SET LONG 9999999
SET SERVEROUTPUT ON
DECLARE
  -- parameters
  p_dir  VARCHAR2(128) := 'DP_DIR';
  p_file VARCHAR2(128) := 'expdp_HERO_20250421_01.dmp';
 
  v_separator   VARCHAR2(80) := '--------------------------------------' ||
                                '--------------------------------------';
  v_path        all_directories.directory_path%TYPE := '?';
  v_filetype    NUMBER;
  v_fileversion VARCHAR2(15);
  v_info_table  sys.ku$_dumpfile_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
  DBMS_OUTPUT.PUT_LINE(v_separator);
  DBMS_OUTPUT.PUT_LINE('Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013');
  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 this block with directory and dumpfile name');
  DBMS_OUTPUT.PUT_LINE('Example..: Set p_dir := ''MY_DIR'' and p_file := ''expdp_s.dmp''');
  DBMS_OUTPUT.PUT_LINE(v_separator);
  DBMS_OUTPUT.PUT_LINE('Filename.: ' || p_file);
  DBMS_OUTPUT.PUT_LINE('Directory: ' || p_dir);
 
  -- Retrieve directory path
  SELECT directory_path INTO v_path
    FROM all_directories
   WHERE directory_name = UPPER(p_dir);
 
  -- Retrieve export dumpfile info
  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
    -- Charset ID to 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 DECODE(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));
        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_values(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;
/

Esse bloco anônimo recebe duas variáveis:
- Nome do diretório:
  - p_dir VARCHAR2(128) := 'DP_DIR';
- Nome do arquivo de dump:
  - p_file VARCHAR2(128) := 'expdp_HERO_20250421_01.dmp';

Resultado:

----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.Version: 18-DEC-2013
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 this block with directory and dumpfile name
Example..: Set p_dir := 'MY_DIR' and p_file := 'expdp_s.dmp'
----------------------------------------------------------------------------
Filename.: expdp_HERO_20250421_01.dmp
Directory: DP_DIR
Disk Path: /backup/DATA_PUMP
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 19.00.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Mon Apr 21 16:39:32 2025
...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....: CDB1
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYS"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 334BD2746E7617D7E063060A0A0AECE8
...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.

O bloco anônimo é uma adaptação do script encontrado no seguinte artigo:

https://databaseinternalmechanism.com/2016/09/13/how-to-read-datapump-dumpfile-header
- Créditos ao autor original da procedure que permitiu a adapção do código acima.

- Até a próxima.

Oracle Data Pump - Get Dump File Details

— Autor: Leonardo Lopes 21/04/2025 12H:32