Shulou(Shulou.com)05/31 Report--

Editor to share with you how to extract exp/expdp export file header information, I hope you will learn something after reading this article, let's discuss it together!

Extract the dump file header information of exp and expdp, and support exp export files after oracle 7.3.4 and expdp export files after oracle 10.1

CREATE OR REPLACE PROCEDURE show_dumpfile_info (

P_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR'

P_file VARCHAR2 DEFAULT 'EXPDAT.DMP')

AS

-- p_dir = directory object where dump file can be found

-- p_file = simple filename of export dump file (case-sensitive)

V_separator VARCHAR2 (80): ='- -'| |

'- -'

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

-- 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: 18Motel DEC muri 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 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), '0mm,' 0 (Conventional Path)'

'1percent,'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 percent, '0.1 (Oracle10g Release 1: 10.1.0.x)'

'1.1 percent, '1.1 (Oracle10g Release 2: 10.2.0.x)'

'2.1 percent, '2.1 (Oracle11g Release 1: 11.1.0.x)'

'3.1 (Oracle11g Release 2: 11.2.0.x)'

'4.1 (Oracle12c Release 1: 12.1.0.x)'

Var_values (1)) INTO var_values (1) FROM dual

SELECT DECODE (var_values (2), '0mm,' 0 (No)','1x,'1 (Yes)'

Var_values (2) INTO var_values (2) FROM dual

SELECT DECODE (var_values (14), '0mm,' 0 (No)','1x,'1 (Yes)'

Var_values (14)) INTO var_values (14) FROM dual

SELECT DECODE (var_values (18), '0mm,' 0 (No)','1x,'1 (Yes)'

Var_values (18)) INTO var_values (18) FROM dual

SELECT DECODE (var_values (19),'0 (No)','1 (Yes)'

Var_values (19) INTO var_values (19) FROM dual

SELECT DECODE (var_values (20), '0mm,' 0 (No)','1x,'1 (Yes)'

Var_values (20)) INTO var_values (20) FROM dual

SELECT DECODE (var_values (21),'0 (No)','1 (Yes)'

Var_values (21) INTO var_values (21) FROM dual

SELECT DECODE (var_values (22)

'1percent,'1 (Unknown)'

'2percent,'2 (None)'

'3percent,'3 (Password)'

'4percent,'4 (Password and Wallet)'

'5years,'5 (Wallet)'

Var_values (22)) INTO var_values (22) FROM dual

SELECT DECODE (var_values (23)

'2percent,'2 (None)'

'3percent,'3 (Basic)'

'4percent,'4 (Low)'

'5years,'5 (Medium)'

'6years,'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

/

After reading this article, I believe you have a certain understanding of "how to extract exp/expdp export header information". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.