Check the version of dump file before import in Oracle

Check the version of dump file before import in Oracle

Check the Version of Dump file before import in Oracle or before give to anyone for import purpose.

1. Create a directory on the Database for checking the file from script.
Note: I am creating test directory which i used in next script also and on windows C:\TEST is the location where dump file is placed.

SQLPLUS / as sysdba

Create directory test as 'C:\TEST';

2. Run the Script by putting the dump file name and directory name.
–Save the script and edit it by putting DUMP_FILE_NAME and DIRECTORY_NAME.
–Execute it on SQLPLUS

SET VERIFY OFF
SET FEEDBACK OFF

DECLARE
ind NUMBER;
fileType NUMBER;
value VARCHAR2(2048);
infoTab KU$_DUMPFILE_INFO := KU$_DUMPFILE_INFO();

BEGIN
--
-- Get the information about the dump file into the infoTab.
--
BEGIN
---DBMS_DATAPUMP.GET_DUMPFILE_INFO('DUMP_FILE_NAME','DIRECTORY_NAME',infoTab,fileType);
DBMS_DATAPUMP.GET_DUMPFILE_INFO('TEST.dmp','TEST',infoTab,fileType); ---------<<<< 0
THEN
DBMS_OUTPUT.PUT_LINE('The information table has ' ||
TO_CHAR(infoTab.COUNT) || ' entries');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------');

ind := infoTab.FIRST;
WHILE ind IS NOT NULL
LOOP
--
-- The following item codes return boolean values in the form
-- of a '1' or a '0'. Display them as 'Yes' or 'No'.
--
value := NVL(infoTab(ind).value, 'NULL');
IF infoTab(ind).item_code IN
(DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT,
DBMS_DATAPUMP.KU$_DFHDR_DIRPATH,
DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED,
DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED,
DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED,
DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED,
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 infoTab(ind).item_code
--
-- The following item codes have been available since Oracle
-- Database 10g, Release 10.2.
--
WHEN DBMS_DATAPUMP.KU$_DFHDR_FILE_VERSION THEN
DBMS_OUTPUT.PUT_LINE('Dump File Version: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT THEN
DBMS_OUTPUT.PUT_LINE('Master Table Present: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_GUID THEN
DBMS_OUTPUT.PUT_LINE('Job Guid: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_FILE_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Dump File Number: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_CHARSET_ID THEN
DBMS_OUTPUT.PUT_LINE('Character Set ID: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_CREATION_DATE THEN
DBMS_OUTPUT.PUT_LINE('Creation Date: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_FLAGS THEN
DBMS_OUTPUT.PUT_LINE('Internal Dump Flags: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_JOB_NAME THEN
DBMS_OUTPUT.PUT_LINE('Job Name: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_PLATFORM THEN
DBMS_OUTPUT.PUT_LINE('Platform Name: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_INSTANCE THEN
DBMS_OUTPUT.PUT_LINE('Instance Name: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_LANGUAGE THEN
DBMS_OUTPUT.PUT_LINE('Language Name: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_BLOCKSIZE THEN
DBMS_OUTPUT.PUT_LINE('Dump File Block Size: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_DIRPATH THEN
DBMS_OUTPUT.PUT_LINE('Direct Path Mode: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED THEN
DBMS_OUTPUT.PUT_LINE('Metadata Compressed: ' || value);
WHEN 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 DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_COUNT THEN
DBMS_OUTPUT.PUT_LINE('Master Table Piece Count: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Master Table Piece Number: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED THEN
DBMS_OUTPUT.PUT_LINE('Table Data Compressed: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED THEN
DBMS_OUTPUT.PUT_LINE('Metadata Encrypted: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED THEN
DBMS_OUTPUT.PUT_LINE('Table Data Encrypted: ' || value);
WHEN DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED THEN
DBMS_OUTPUT.PUT_LINE('TDE Columns Encrypted: ' || value);

--
-- For the DBMS_DATAPUMP.KU$_DFHDR_ENCRYPTION_MODE item code a
-- numeric value is returned. So examine that numeric value
-- and display an appropriate name value for it.
--
WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCRYPTION_MODE THEN
CASE TO_NUMBER(value)
WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_NONE THEN
DBMS_OUTPUT.PUT_LINE('Encryption Mode: None');
WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_PASSWORD THEN
DBMS_OUTPUT.PUT_LINE('Encryption Mode: Password');
WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_DUAL THEN
DBMS_OUTPUT.PUT_LINE('Encryption Mode: Dual');
WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_TRANS THEN
DBMS_OUTPUT.PUT_LINE('Encryption Mode: Transparent');
END CASE;

--
-- The following item codes were introduced in Oracle Database 12c
-- Release 12.1
--

--
-- For the DBMS_DATAPUMP.KU$_DFHDR_COMPRESSION_ALG item code a
-- numeric value is returned. So examine that numeric value and
-- display an appropriate name value for it.
--
WHEN DBMS_DATAPUMP.KU$_DFHDR_COMPRESSION_ALG THEN
CASE TO_NUMBER(value)
WHEN DBMS_DATAPUMP.KU$_DFHDR_CMPALG_NONE THEN
DBMS_OUTPUT.PUT_LINE('Compression Algorithm: None');
WHEN DBMS_DATAPUMP.KU$_DFHDR_CMPALG_BASIC THEN
DBMS_OUTPUT.PUT_LINE('Compression Algorithm: Basic');
WHEN DBMS_DATAPUMP.KU$_DFHDR_CMPALG_LOW THEN
DBMS_OUTPUT.PUT_LINE('Compression Algorithm: Low');
WHEN DBMS_DATAPUMP.KU$_DFHDR_CMPALG_MEDIUM THEN
DBMS_OUTPUT.PUT_LINE('Compression Algorithm: Medium');
WHEN DBMS_DATAPUMP.KU$_DFHDR_CMPALG_HIGH THEN
DBMS_OUTPUT.PUT_LINE('Compression Algorithm: High');
END CASE;
ELSE NULL; -- Ignore other, unrecognized dump file attributes.
END CASE;
ind := infoTab.NEXT(ind);
END LOOP;
END IF;
END;
/

4. Output got from the SCRIPT:

--------------------------------------------
Information for file: example1.dmp
example1.dmp is a Data Pump dump file
The information table has 22 entries
---------------------------------------------
Dump File Version:         4.1
Database Version:          12.01.00.00.00
Master Table Present:      Yes
Internal Dump Flags:       514
Job Guid:                  FE0962020B4645E1A71EB371BA8AE0CE
Character Set ID:          178
Dump File Number:          1
Job Name:                  "SYS"."SYS_EXPORT_SCHEMA_01"
Platform Name:             IBMPC/WIN_NT64-9.1.0
Instance Name:             scott
Language Name:             WE8MSWIN1252
Creation Date:             Thu Jun 30 02:17:16 2016
Dump File Block Size:      4096
Metadata Compressed:       Yes
Table Data Compressed:     No
Compression Algorithm:     Basic
Metadata Encrypted:        No
Table Data Encrypted:      No
TDE Columns Encrypted:     No
Encryption Mode:           None
Master Table Piece Count:  1
Master Table Piece Number: 1

Note: In this you got the information about dump which database version, Dump file version , creation date etc.

For more info refer the link:
https://docs.oracle.com/database/121/SUTIL/GUID-5AAC848B-5A2B-4FD1-97ED-D3A048263118.htm#SUTIL977

2 thoughts on “Check the version of dump file before import in Oracle

  1. Pingback: ORA-39142: incompatible version number 4.2 in dump file | Smart way of Technology

  2. Yasir

    SQL> @verify_exp-dmp.sql
    THEN
    *
    ERROR at line 14:
    ORA-06550: line 14, column 1:
    PLS-00103: Encountered the symbol “THEN” when expecting one of the following:
    ( begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with

    << continue close current delete fetch lock
    insert open rollback savepoint set sql execute commit forall
    merge pipe purge
    ORA-06550: line 144, column 5:
    PLS-00103: Encountered the symbol "IF" when expecting one of the following:
    ;

    current delete exists prior

    I went ahead and commented as below :
    –THEN
    DBMS_OUTPUT.PUT_LINE(‘The information table has ‘ ||

    Ran the script again but failed –

    SQL> @verify_exp-dmp.sql
    END IF;
    *
    ERROR at line 144:
    ORA-06550: line 144, column 5:
    PLS-00103: Encountered the symbol “IF” when expecting one of the following:
    ;

    The symbol “IF” was ignored.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.