[20220128]Check the datapump file header information in Oracle.txt
[20220128]Check the datapump file header information in Oracle.txt
--//昨天看了連結:smarttechways.com/2022/01/17/check-the-datapump-file-header-information-in-oracle/,自己嘗試看看.
1.環境:
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ expdp scott/book
Export: Release 11.2.0.4.0 - Production on Fri Jan 28 09:12:57 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/a********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30.18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T1" 23.84 MB 999999 rows
. . exported "SCOTT"."TEST" 149.1 KB 1 rows
. . exported "SCOTT"."JOB_TIMES" 5.867 KB 3 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.968 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/book/dpdump/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jan 28 09:13:23 2022 elapsed 0 00:00:25
SYS@book> select * from DBA_DIRECTORIES where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/
SYS@book> grant execute on show_dumpfile_info to sys;
grant execute on show_dumpfile_info to sys
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
--//我發現根本沒有這個儲存過程,檢索找到如下,做一個記錄.
CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP') AUTHID CURRENT_USER
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: 18-DEC-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), '0', '0 (Conventional Path)',
'1', '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', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
'4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Password and Wallet)',
'5', '5 (Wallet)',
var_values(22)) INTO var_values(22) FROM dual;
SELECT DECODE(var_values(23),
'2', '2 (None)',
'3', '3 (Basic)',
'4', '4 (Low)',
'5', '5 (Medium)',
'6', '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;
/
SYS@book> exec show_dumpfile_info(p_dir=> 'DATA_PUMP_DIR', p_file=> 'expdat.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: expdat.dmp
Directory: DATA_PUMP_DIR
Disk Path: /u01/app/oracle/admin/book/dpdump/
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 11.02.00.04.00
...Internal Dump File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)
...Creation Date.................: Fri Jan 28 09:13:23 2022
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: book
...Characterset ID of source db..: 852 (ZHS16GBK)
...Language Name of characterset.: ZHS16GBK
...Job Name......................: "SCOTT"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: D69B229EFD13D6B9E0534E64A8C0D70D
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 2
...Max Items Code (Info Items)...: 22
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2854371/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Warning: Cannot modify header information - headers already sent by (output started at file_path:line)HeaderORM
- oracle10g 資料檔案頭data file header(file header)OracleHeader
- The Ultimate (DLL) Header File (轉)Header
- [Oracle Script] check File I/OOracle
- a control file contains informationAIORM
- PE教程3: File Header （檔案頭）Header
- Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)ORMSQL
- Script toCollect Log File Sync Diagnostic Information (lfsdiag.sql)ORMSQL
- ERROR: Check /etc/pam.conf file permissions and ownershipError
- ORA-01251: Unknown File Header Version read for file number 7Header
- mha_replication_check Can't exec "mysqlbinlog": No such file or directoryMySql
- contains a file system with errors, check forced解決方法AIError
- Log check error: Log file /var/log/messages is not readable!Error
- Please Check VKTM Trace File for More Details (文件 ID 1347586.1)AI
- OGG-01453 Database login information not specified in parameter fileDatabaseORM
- LMT更新file header bitmap不產生redo和undo ?Header
- gulp使用gulp-file-include將header/footer引入頁面Header
- 【DATAPUMP】使用DataPump遷移Oracle資料庫Oracle資料庫
- Check mod for temporary file : /usr/local/centreon/var/lib/centplugins/Plugin
- how to check unsolved conflicts file list in git merge?Git
- about datapump parallelParallel
- Speedup Datapump ImportImport
- Fatal error: can't check configuration file '/home/watt/proxy/dError
- 20161110Bitmapped File Space Header恢復APPHeader
- [20121011]file header reset--bbed學習.txtHeader
- datapump簡介(一)
- Oracle DataPump之二Oracle
- Oracle DataPump之一Oracle
- Oracle Datapump 學習Oracle
- Uncertainy and informationAIORM
- System File1 File Header（資料庫System檔案1檔案頭）損壞情況的恢復Header資料庫
- 如何提高datapump操作效能
- 外部表筆記-datapump筆記
- ORACLE_DATAPUMP & External TableOracle
- redhat系統伺服器重啟後提示An error occurred during the file system check.Redhat伺服器Error
- 使用datapump 匯出匯入同義詞(export and import synonym using datapump)ExportImport
- Vi and Vim Autocommand: 3 Steps to Add Custom Header To Your File AutomaticallyHeader
- ORA-00205: error in identifying control file, check alert log for more infoErrorIDE