联系：QQ(5163721)
标题：对于已经存在的exp导出文件或者expdp的导出文件，如何获取导出文件头的信息呢？
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
对于已经存在的exp导出文件或者expdp的导出文件，如何获取导出文件头的信息呢？
我们来测试下，我的expdp导出文件是：
[oracle@lunar datapump]$ pwd /u01/test/datapump [oracle@lunar datapump]$ ll total 452 -rw-r----- 1 oracle asmadmin 458752 Aug 30 14:10 sp2014.dmp [oracle@lunar datapump]$
方法1：利用dbms_datapump.get_dumpfile_info我们可以得到dump文件头的信息，具体脚本参考
[oracle@lunar test]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 30 18:55:43 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SYS@lunarbb>exec show_dumpfile_info(p_dir=> 'lunar_dir', p_file=> 'sp2014.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.: sp2014.dmp
Directory: lunar_dir
Disk Path: /u01/test/datapump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.00.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Fri Aug 30 14:10:32 2013
...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....: lunarbb
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "LUNAR"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: E5250CB5A94A67ECE0430100007F08B5
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 23
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27
SYS@lunarbb>
方法2： 使用string来看：
[oracle@lunar datapump]$ cat sp2014.dmp | head | strings |more "LUNAR"."SYS_EXPORT_TABLE_01" ----job名称 x86_64/Linux 2.4.xx -------操作系统版本 lunarbb ----------导出文件的数据库名字 AL32UTF8 -------导出文件的字符集 12.01.00.00.00 -----导出文件的版本信息 001:001:000001:000001 。。。
方法3：
impdp lunar/lunar DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300
这里TRACE=100300是生成data pump进程trace信息。其他trace信息还有很多，后面陆续会介绍其他的data pump的相关trace。
我们知道data pump进程启动的时候，会有两类进程，即：Datapump Master (DM) 和 Worker (DW) processes
他们生成的trace文件产生在BACKGROUND_DUMP_DEST目录里面，命名格式如下：
— Master Process trace file:
— Worker Process trace file:
[oracle@lunar trace]$ ls -lrt *dw* -rw-r----- 1 oracle oinstall 352 Mar 4 04:58 travel_dw00_3097.trm -rw-r----- 1 oracle oinstall 9091 Mar 4 04:58 travel_dw00_3097.trc [oracle@lunar trace]$ ls -lrt *dm00* -rw-r----- 1 oracle oinstall 421 Mar 4 04:58 travel_dm00_3095.trm -rw-r----- 1 oracle oinstall 7057 Mar 4 04:58 travel_dm00_3095.trc [oracle@lunar trace]$
我们具体看一下3个文件的内容：
1，SQLFILE=lunartest_impdp.sql
2，travel_dw00_3097.trc
3，travel_dm00_3095.trc
首先，看下lunartest_impdp.sql，这里面SQLFILE类似于imp工具的INDEXFILE参数，即生成dump文件的DDL信息：
[oracle@lunar datapump]$ impdp lunar/lunar DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300 Import: Release 11.2.0.3.0 - Production on Tue Mar 4 04:57:58 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Master table "LUNAR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded Starting "LUNAR"."SYS_SQL_FILE_TABLE_01": lunar/******** DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300 Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Job "LUNAR"."SYS_SQL_FILE_TABLE_01" successfully completed at 04:58:00 [oracle@lunar datapump]$
lunartest_impdp.sql中包含了dump文件中的DDL信息，但是相比以前的exp的indexfile参数生成的DDL文本信息的可读性，好了不是一星半点，O(∩_∩)O哈哈~：
[oracle@lunar datapump]$ cat lunartest_impdp.sql
-- CONNECT LUNAR
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "LUNAR"."LUNAR_PAR_TEST"
( "NAME" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
"AAAAA" NUMBER NOT NULL ENABLE,
"BBBBB" VARCHAR2(180 BYTE) NOT NULL ENABLE,
"CCCCC" VARCHAR2(4000 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("AAAAA") INTERVAL (1) TRANSITION ("PART_INIT")
SUBPARTITION BY RANGE ("BBBBB")
SUBPARTITION TEMPLATE (
SUBPARTITION "SP_2008" VALUES LESS THAN ( '2009' ),
SUBPARTITION "SP_2009" VALUES LESS THAN ( '2010' ),
SUBPARTITION "SP_2010" VALUES LESS THAN ( '2011' ),
SUBPARTITION "SP_2011" VALUES LESS THAN ( '2012' ),
SUBPARTITION "SP_2012" VALUES LESS THAN ( '2013' ),
SUBPARTITION "SP_2013" VALUES LESS THAN ( '2014' ),
SUBPARTITION "SP_2014" VALUES LESS THAN ( '2015' ),
........
省略部分内容
........
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP77" VALUES LESS THAN ('2025')
TABLESPACE "USERS"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP78" VALUES LESS THAN ('2026')
TABLESPACE "USERS"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP79" VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS"
NOCOMPRESS ) ) ENABLE ROW MOVEMENT ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "LUNAR"."PK_TEST_COM_PARTITION_1" ON "LUNAR"."LUNAR_PAR_TEST" ("NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 167
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "LUNAR"."PK_TEST_COM_PARTITION_1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "LUNAR"."LUNAR_PAR_TEST" ADD CONSTRAINT "PK_TEST_COM_PARTITION_1" PRIMARY KEY ("NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 167
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
[oracle@lunar datapump]$
2，travel_dw00_3097.trc
从下面的trace可以看到，该文件主要是Data Pump Worker进程的详细操作过程：
KUPP:04:57:59.427: Current trace/debug flags: 00100300 = 1049344 *** MODULE NAME:(Data Pump Worker) 2014-03-04 04:57:59.436 *** ACTION NAME:(SYS_SQL_FILE_TABLE_01) 2014-03-04 04:57:59.436 KUPC:04:57:59.436: Setting remote flag for this process to FALSE prvtaqis - Enter prvtaqis subtab_name upd prvtaqis sys table upd KUPF:04:57:59.489: In INIT_CB kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 kwqberlst rqan->lascn_kwqiia > 0 block kwqberlst rqan->lascn_kwqiia 4 kwqberlst ascn 511695 lascn 22 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 KUPF:04:57:59.499: Retrieved FILE_LIST, Count = 1 KUPF:04:57:59.499: dump file block size: 4096 KUPF:04:57:59.499: metadata buffer size: 131072 KUPF:04:57:59.499: table data buffer size: 262144 KUPF:04:57:59.499: min phy block size: 512 KUPF:04:57:59.499: max phy block size: 32768 KUPF:04:57:59.499: metadata compression: 1 KUPF:04:57:59.499: tabldata compression: 0 KUPF:04:57:59.499: metadata encryption: 0 KUPF:04:57:59.499: tabldata encryption: 0 KUPF:04:57:59.499: column encryption: 0 KUPF:04:57:59.499: job version: 11.02.00.03.00 KUPF:04:57:59.499: (kupfxInit) File Manager has been initialized... ....... 省略部分 ......... *** 2014-03-04 04:58:00.251 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 kwqberlst rqan->lascn_kwqiia > 0 block kwqberlst rqan->lascn_kwqiia 4 kwqberlst ascn 511695 lascn 22 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 KUPF:04:58:00.255: MD: filePiece(1).fid: 1 KUPF:04:58:00.255: MD: filePiece(1).bno: 2 KUPF:04:58:00.255: MD: filePiece(1).len: 1075 KUPF:04:58:00.255: MD: filePiece(1).alc: .1075 KUPF:04:58:00.255: MD: filePiece(1).off: 0 KUPF:04:58:00.255: MD: filePiece(1).nam: /home/oracle/test/datapump/lunartest.dmp KUPF:04:58:00.255: In kupfxReadLob... KUPF:04:58:00.255: In kupfiReadLob... KUPF:04:58:00.255: In kupfiSetupMDFilePiece... KUPF:04:58:00.255: In kupfuAllocFilePiece... KUPF:04:58:00.255: In kupfioOpenForRead... KUPF:04:58:00.255: In kupfioOpenForRead: file /home/oracle/test/datapump/lunartest.dmp has 63 block(s) KUPF:04:58:00.255: In kupfTransformData... ......... 省略部分 ......... KUPF:04:58:00.314: In kupfuDecompress... KUPF:04:58:00.314: ...processing input piece number: 1 KUPF:04:58:00.314: ...available in: 1421 KUPF:04:58:00.314: ...available out: 131072 KUPF:04:58:00.316: ...decompressor encountered EOS. KUPF:04:58:00.316: ...writing 8400 bytes to CLOB KUPF:04:58:00.316: ...wrote 4200 chars to CLOB at offset 1 KUPF:04:58:00.316: In kupfuiTrxCleanup...4 KUPF:04:58:00.316: MD: XML read complete... KUPF:04:58:00.316: MD: Input: 1421 bytes Output: 4200 bytes KUPF:04:58:00.379: In kupfxCloseCtx... KUPF:04:58:00.379: In kupfiCloseCtx... KUPF:04:58:00.379: In kupfioCloseFile... KUPF:04:58:00.379: File number 1 has been closed KUPF:04:58:00.379: Leaving kupfiCloseCtx... kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 kwqberlst rqan->lascn_kwqiia > 0 block kwqberlst rqan->lascn_kwqiia 4 kwqberlst ascn 511695 lascn 22 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 KUPF:04:58:00.385: In kupfxTerm... [oracle@lunar trace]$
3，travel_dm00_3095.trc
从下面的trace可以看到，该文件主要是Data Pump Master进程的详细操作过程：
KUPP:04:57:59.157: Current trace/debug flags: 00100300 = 1049344 *** MODULE NAME:(Data Pump Master) 2014-03-04 04:57:59.163 *** ACTION NAME:(SYS_SQL_FILE_TABLE_01) 2014-03-04 04:57:59.163 KUPC:04:57:59.163: Setting remote flag for this process to FALSE prvtaqis - Enter prvtaqis subtab_name upd prvtaqis sys table upd KUPP:04:57:59.201: Initialization complete for master process DM00 KUPF:04:57:59.302: In INIT_CB KUPF:04:57:59.303: dump file block size: 4096 KUPF:04:57:59.303: metadata buffer size: 131072 KUPF:04:57:59.303: table data buffer size: 262144 KUPF:04:57:59.303: min phy block size: 512 KUPF:04:57:59.303: max phy block size: 32768 KUPF:04:57:59.303: metadata compression: 0 KUPF:04:57:59.303: tabldata compression: 0 KUPF:04:57:59.303: metadata encryption: 0 KUPF:04:57:59.303: tabldata encryption: 0 KUPF:04:57:59.303: column encryption: 0 KUPF:04:57:59.303: job version: 11.02.00.03.00 KUPF:04:57:59.303: (kupfxInit) File Manager has been initialized... ......... 省略部分 ......... kwqberlst ascn 511682 lascn 22 KUPF:04:57:59.378: is_dba = TRUE KUPF:04:57:59.378: read_only = TRUE KUPF:04:57:59.378: fileName = lunartest.dmp ------------导出文件名称 KUPF:04:57:59.379: directory = LUNAR_DIR ------------directory的名称 KUPF:04:57:59.379: In kupfxParseFileName... KUPF:04:57:59.379: directory = LUNAR_DIR KUPF:04:57:59.379: In kupfxGetDefFileName... KUPF:04:57:59.380: In kupfxExmDmpFile... KUPF:04:57:59.380: In kupfuExmDmpFile... KUPF:04:57:59.380: In kupfioReadHeader... KUPF:04:57:59.380: newImpFile: EXAMINE_DUMP_FILE KUPF:04:57:59.380: ......DB Version = 11.02.00.03.00 ------------这里就是数据库版本信息 KUPF:04:57:59.380: File Version Str = 3.1 KUPF:04:57:59.380: File Version Num = 769 KUPF:04:57:59.380: Version CapBits1 = 98559 KUPF:04:57:59.380: ......Has Master = 1 KUPF:04:57:59.380: ........Job Guid = F3C86AFC53A40BEFE0434238A8C03446 KUPF:04:57:59.380: Master Table Pos = 8 KUPF:04:57:59.380: Master Table Len = 226992 KUPF:04:57:59.380: Master Table Fsi = 001:001:000001:000001 KUPF:04:57:59.380: .....File Number = 1 KUPF:04:57:59.380: ......Charset ID = 873 ------------这里就是字符集ID KUPF:04:57:59.381: ...Creation date = Tue Mar 04 04:55:39 2014 KUPF:04:57:59.381: ...........Flags = 2 KUPF:04:57:59.381: ......Media Type = 0 KUPF:04:57:59.381: ........Job Name = "LUNAR"."SYS_EXPORT_TABLE_01" ------------这里就是job信息 KUPF:04:57:59.381: ........Platform = x86_64/Linux 2.4.xx KUPF:04:57:59.381: ........Instance = travel KUPF:04:57:59.381: ........Language = AL32UTF8 ------------这里就是字符集信息 KUPF:04:57:59.381: .......Blocksize = 4096 KUPF:04:57:59.386: Added FILE row; PO: -22; FID: 1; NAM: lunartest.dmp KUPF:04:57:59.386: newImpFile: file; /home/oracle/test/datapump/lunartest.dmp, FID; 1 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 4 kwqberlst rqan->lascn_kwqiia > 0 block kwqberlst rqan->lascn_kwqiia 4 kwqberlst ascn 511682 lascn 22 KUPF:04:57:59.391: In kupfxGetDefFileName... KUPF:04:57:59.391: is_dba = TRUE KUPF:04:57:59.391: read_only = FALSE KUPF:04:57:59.391: fileName = lunartest_impdp.sql KUPF:04:57:59.391: directory = LUNAR_DIR KUPF:04:57:59.391: In kupfxParseFileName... KUPF:04:57:59.392: directory = LUNAR_DIR ......... 省略部分 .........
