148 lines
8.1 KiB
SQL
148 lines
8.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datapump dumpfile information
|
|
* Versions : 10.2 and above
|
|
* Parameter : 1 - DIRECTORY
|
|
* 2 - Datapump dumpfile
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ --------------------------------------------------------
|
|
* 19-JUN-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
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
|
|
--
|
|
-- The following item codes return boolean values in the form
|
|
-- of a '1' or a '0'. We'll display them as 'Yes' or 'No'.
|
|
--
|
|
value := NVL(dumpfile_info(ind).value, 'NULL');
|
|
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
|
|
--
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_COUNT THEN DBMS_OUTPUT.PUT_LINE('Master Table Piece Count: ' || value);
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Master Table Piece Number: ' || value);
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED THEN DBMS_OUTPUT.PUT_LINE('Table Data Compressed: ' || value);
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED THEN DBMS_OUTPUT.PUT_LINE('Metadata Encrypted: ' || value);
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED THEN DBMS_OUTPUT.PUT_LINE('Table Data Encrypted: ' || value);
|
|
&&_IF_ORA_11gR1_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED THEN DBMS_OUTPUT.PUT_LINE('TDE Columns Encrypted: ' || value);
|
|
|
|
|
|
--
|
|
-- The following item codes were introduced in Oracle Database 11g
|
|
-- Release 11.2.0.4
|
|
--
|
|
--
|
|
-- For the sys.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.
|
|
--
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCRYPTION_MODE THEN
|
|
&&_IF_ORA_11204_OR_HIGHER CASE TO_NUMBER(value)
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_UNKNOWN THEN DBMS_OUTPUT.PUT_LINE('Encryption Mode: Unknown');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_NONE THEN DBMS_OUTPUT.PUT_LINE('Encryption Mode: None');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_PASSWORD THEN DBMS_OUTPUT.PUT_LINE('Encryption Mode: Password');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_DUAL THEN DBMS_OUTPUT.PUT_LINE('Encryption Mode: Dual');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_TRANS THEN DBMS_OUTPUT.PUT_LINE('Encryption Mode: Transparent');
|
|
&&_IF_ORA_11204_OR_HIGHER END CASE;
|
|
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE THEN
|
|
&&_IF_ORA_11204_OR_HIGHER CASE TO_NUMBER(value)
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE_UNKNOWN THEN DBMS_OUTPUT.PUT_LINE('Encryption Password Mode: Unknown');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE_NONE THEN DBMS_OUTPUT.PUT_LINE('Encryption Password Mode: None');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE_PASSWORD THEN DBMS_OUTPUT.PUT_LINE('Encryption Password Mode: Password');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE_DUAL THEN DBMS_OUTPUT.PUT_LINE('Encryption Password Mode: Dual');
|
|
&&_IF_ORA_11204_OR_HIGHER WHEN sys.DBMS_DATAPUMP.KU$_DFHDR_ENCPWD_MODE_TRANS THEN DBMS_OUTPUT.PUT_LINE('Encryption Password Mode: Transparent');
|
|
&&_IF_ORA_11204_OR_HIGHER END CASE;
|
|
|
|
ELSE
|
|
NULL; -- Unrecognized dump file attributes.
|
|
DBMS_OUTPUT.PUT_LINE('Item Code ' || dumpfile_info(ind).item_code || ' : ' || value);
|
|
END CASE;
|
|
ind := dumpfile_info.NEXT(ind);
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
|
|
@@footer |