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> @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@xxxxxxxxxxxxx> on behalf of Vanita Sharma Tyagi
<dba.vanitasharma@xxxxxxxxx>
Reply-To: <dba.vanitasharma@xxxxxxxxx>
Date: Saturday, 4 April 2020 at 10:31
To: Oracle-L oracle-l <Oracle-L@xxxxxxxxxxxxx>
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
