Re: Expdp Dump File Infomation

Vanita,  

You can use the script given below to find out how many files are in the export. For example in sample output, below there 7 files in the original export. You can use any datapump export file to file this information.  

Master Table Piece Count:  7

Master Table Piece Number: 1    

Script Output  

SQL> _at_datapump_dumpfile_info DATA_PUMP_DIR SYSTEMP01.dmp  

Datapump File Info

The information table has 22 entries

Dump File Version:         4.1

Database Version:          12.00.00.00.00

Master Table Present:      Yes

Internal Dump Flags:       514

Job Guid:                  A11380AF5EBD33CAE0532164A8C0FEC5

Character Set ID:          31

Dump File Number:          1

Job Name:                  "SYS"."SYS_EXPORT_SCHEMA_01"

Platform Name:             x86_64/Linux 2.4.xx

Instance Name:             12102_RHEL6

Language Name:             WE8ISO8859P1

Creation Date:             Tue Mar 17 20:08:54 2020

Dump File Block Size:      4096

Metadata Compressed:       Yes

Table Data Compressed:     No

Item Code 23 :             3

Metadata Encrypted:        No

Table Data Encrypted:      No

TDE Columns Encrypted:     No

Encryption Mode:           None

Master Table Piece Count:  7

Master Table Piece Number: 1    

Script  

set serveroutput on  

VARIABLE directory VARCHAR2(30);

VARIABLE dumpfile VARCHAR2(300);  

set term off

BEGIN    :directory := upper('&&1');

   :dumpfile := '&&2';

END;
/

set term on

DECLARE   ind            NUMBER;              -- Loop index

  dumpfile_info  KU$_DUMPFILE_INFO;

  filetype       NUMBER;

  value          VARCHAR2(2048);

BEGIN      sys.DBMS_DATAPUMP.get_dumpfile_info(filename  => :dumpfile

                                       , directory => :directory

                                       , info_table => dumpfile_info

                                       , filetype   => filetype

                                       );

     dbms_output.put_line(' ' );

     dbms_output.put_line('------------------------------------------' ); 

     dbms_output.put_line('Datapump File Info' );

     dbms_output.put_line('------------------------------------------' ); 

     dbms_output.put_line('The information table has ' || TO_CHAR(dumpfile_info.COUNT) || ' entries');  

     ind := dumpfile_info.first;

     while ind is not null

     loop  

         value := NVL(dumpfile_info(ind).value, 'NULL');

         --

         -- The following item codes return boolean values in the form

         -- of a '1' or a '0'. We'll display them as 'Yes' or 'No'.

         --

         IF dumpfile_info(ind).item_code IN

            (sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT,

             sys.DBMS_DATAPUMP.KU$_DFHDR_DIRPATH,

             sys.DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED,

             sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED,

             sys.DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED,

             sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED,

             sys.DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED)

         THEN            CASE value

             WHEN '1' THEN value := 'Yes';

             WHEN '0' THEN value := 'No';

           END CASE;          END IF;            --

         -- Display each item code with an appropriate name followed by

         -- its value.

         --

         CASE dumpfile_info(ind).item_code

           --

           -- The following item codes have been available since Oracle Database 10g

           -- Release 10.2.

           --

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_FILE_VERSION        THEN DBMS_OUTPUT.PUT_LINE('Dump File Version:         ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT      THEN DBMS_OUTPUT.PUT_LINE('Master Table Present:      ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_GUID                THEN DBMS_OUTPUT.PUT_LINE('Job Guid:                  ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_FILE_NUMBER         THEN DBMS_OUTPUT.PUT_LINE('Dump File Number:          ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_CHARSET_ID          THEN DBMS_OUTPUT.PUT_LINE('Character Set ID:          ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_CREATION_DATE       THEN DBMS_OUTPUT.PUT_LINE('Creation Date:             ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_FLAGS               THEN DBMS_OUTPUT.PUT_LINE('Internal Dump Flags:       ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_JOB_NAME            THEN DBMS_OUTPUT.PUT_LINE('Job Name:                  ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_PLATFORM            THEN DBMS_OUTPUT.PUT_LINE('Platform Name:             ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_INSTANCE            THEN DBMS_OUTPUT.PUT_LINE('Instance Name:             ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_LANGUAGE            THEN DBMS_OUTPUT.PUT_LINE('Language Name:             ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_BLOCKSIZE           THEN DBMS_OUTPUT.PUT_LINE('Dump File Block Size:      ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DIRPATH             THEN DBMS_OUTPUT.PUT_LINE('Direct Path Mode:          ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED THEN DBMS_OUTPUT.PUT_LINE('Metadata Compressed:       ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DB_VERSION          THEN DBMS_OUTPUT.PUT_LINE('Database Version:          ' || value);

           --

           -- The following item codes were introduced in Oracle Database 11g

           -- Release 11.1

           --

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_COUNT  THEN DBMS_OUTPUT.PUT_LINE('Master Table Piece Count:  ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Master Table Piece Number: ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED     THEN DBMS_OUTPUT.PUT_LINE('Table Data Compressed:     ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED  THEN DBMS_OUTPUT.PUT_LINE('Metadata Encrypted:        ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED      THEN DBMS_OUTPUT.PUT_LINE('Table Data Encrypted:      ' || value);

           WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED   THEN DBMS_OUTPUT.PUT_LINE('TDE Columns Encrypted:     ' || value);

         END CASE;         

         ind := dumpfile_info.NEXT(ind);

     end loop;

end;

/
 

Regards,

Vishal Gupta  

From: <oracle-l-bounce_at_freelists.org> on behalf of Vanita Sharma Tyagi <dba.vanitasharma_at_gmail.com> Reply-To: <dba.vanitasharma_at_gmail.com> Date: Saturday, 4 April 2020 at 10:31
To: Oracle-L oracle-l <Oracle-L_at_freelists.org> Subject: Expdp Dump File Infomation  

Hi Experts ,  

Need some help in validating the dump files in a expdp dump set . I followed oracle metalink 462488.1 but no where i can see any information about how many files are present in my dumpset .  

If someone has given a dumpset to you and before import itself we want to make sure that dumpset is complete . [ Without using impdp sqlfile option ] We have access via sqlplus only to system views/packages to check .    

BR

Vanita

--
http://www.freelists.org/webpage/oracle-l