每个dump文件头部都包含文件头信息，通常块的大小为4k，头部包含了数据库名称，导出时间，导出的平台，数据库的版本，字符集等主要信息。从Oracle10g Release 2 (10.2.0.1.0)开始，可以通过包dbms_datapump.get_dumpfile_info来获取这些信息，当然也可以通过aodu获取。本文的测试是在12c和11g下做的测试。
获取头部信息的procedure show_dumpfile_info
CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'dpump',
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: 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;
/
12c dmp文件头部信息
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB11 READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/cdb1/undotbs01.dbf
/oradata/cdb1/pdb11/system01.dbf
/oradata/cdb1/pdb11/sysaux01.dbf
/oradata/cdb1/pdb11/pdb11_users01.dbf
SQL> create tablespace ohs_tbs datafile '/oradata/cdb1/pdb11/ohs_tbs01.dbf' size 200M;
Tablespace created.
SQL> create user ohsdba identified by ohsdba default tablespace ohs_tbs;
User created.
SQL> grant dba to ohsdba;
Grant succeeded.
SQL>
SQL> conn ohsdba/ohsdba@localhost:/pdb11
Connected.
SQL>
SQL> create table ohs_objects as select * from dba_objects;
Table created.
SQL> insert into ohs_objects select * from ohs_objects;
90926 rows created.
SQL> /
181852 rows created.
SQL> /
363704 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create directory dpump as '/pgold/dpump';
Directory created.
SQL>
[oracle@db1 ~]$ expdp ohsdba/ohsdba@localhost:/pdb11 directory=dpump dumpfile=pgold_pdb11_%U.dmp logfile=expdp_20160612.log filesize=5M parallel=5
Export: Release 12.1.0.2.0 - Production on Sun Jun 12 10:51:58 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01": ohsdba/********@localhost:/pdb11 directory=dpump dumpfile=pgold_pdb11_%U.dmp logfile=expdp_20160612.log filesize=5M parallel=5 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 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/PRE_SCHEMA/PROCACT_SCHEMA . . exported "OHSDBA"."OHS_OBJECTS" 82.86 MB 727408 rows Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/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/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is: /pgold/dpump/pgold_pdb11_01.dmp /pgold/dpump/pgold_pdb11_02.dmp /pgold/dpump/pgold_pdb11_03.dmp /pgold/dpump/pgold_pdb11_04.dmp /pgold/dpump/pgold_pdb11_05.dmp /pgold/dpump/pgold_pdb11_06.dmp /pgold/dpump/pgold_pdb11_07.dmp /pgold/dpump/pgold_pdb11_08.dmp /pgold/dpump/pgold_pdb11_09.dmp /pgold/dpump/pgold_pdb11_10.dmp /pgold/dpump/pgold_pdb11_11.dmp /pgold/dpump/pgold_pdb11_12.dmp /pgold/dpump/pgold_pdb11_13.dmp /pgold/dpump/pgold_pdb11_14.dmp /pgold/dpump/pgold_pdb11_15.dmp /pgold/dpump/pgold_pdb11_16.dmp /pgold/dpump/pgold_pdb11_17.dmp /pgold/dpump/pgold_pdb11_18.dmp Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 10:52:39 2016 elapsed 0 00:00:39 [oracle@db1 ~]$
SQL> set serveroutput on
SQL> exec show_dumpfile_info(p_file=>'pdb11_01.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.: pdb11_01.dmp
Directory: dpump
Disk Path: /pgold/dpump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.02.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Sun Jun 12 10:50:28 2016
...File Number (in dump file set): 1
...Master Present in dump file...: 0 (No)
...Master in how many dump files.:
...Master Piece Number in file...:
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: cdb1
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 350CD52F3B8F10A3E0537800000A550F
...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.
SQL>
[oracle@db1 ~]$ ./aodu
AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 11:07:57 2016
Copyright (c) 2014, 2015, Robin.Han. All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> help
Available Commands:
help - Display this help
dul - oracle data unload,currently under developing
unpump - extract data from dmp files,currently under developing
asmcmd - asmcmd utility,currently under developing
bbed - bbed utility,currently under developing
exit - Exits
asmdisk - Check asm disks(e.g. asmdisk /dev/sdb1)
unwrap - Unwrap PL/SQL,e.g. unwrap prvtstat.plb prvtstat.sql
dmpinfo - Get dmpfile(exp/expdp) info,e.g. dmpinfo ohsdba.dmp
asm - ASM utility,internal use only
rac - RAC utility,internal use only
optim - Optimizer utility,internal use only
ora600 - ORA-600 searching
oradebug - Oradebug utility,oradebug usage all
host - Enter OS terminal
rowid - Decode rowid,e.g. rowid AAAAECAABAAAAgiAAA
scn - Decode scn,e.g. scn 0x0000.00233d44
rdba - Convert RDBA into rfile# and block#,e.g. rdba 0x004062ff
time - Convert the number to checkpoint time,e.g. time 123456789
drux - Decode rba/xid/uba,e.g. drux rba 0x000024.000011bd.0010
odlog - Get archived/redo logfile info. odlog <filename>
AODU> dmpinfo /pgold/dpump/pgold_pdb11_18.dmp
Dmp Filetype..................: Datapump dumpfile
Source Database Name..........:
Database Job Version..........:
Internal Dump File Version....: 4.1
Creation Date.................: 2016-06-12 10:52:39
Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
GUID (unique job identifier)..: 350CDB00668A10D6E0537800A49C8
Block size dump file (bytes)..: 4096
Language Name of characterset.:
Characterset ID of source db..: 873
Operating System of source db.:
AODU>
11g dmp文件头部信息
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options SQL> SQL> select name from v$database; NAME --------- UPGR SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/upgr/system01.dbf /oradata/upgr/sysaux01.dbf /oradata/upgr/undotbs01.dbf /oradata/upgr/users01.dbf SQL> create tablespace ohs_tbs11g datafile '/oradata/upgr/ohs_tbs11g.dbf' size 200M; Tablespace created. SQL> create user ohsdba identified by ohsdba default tablespace ohs_tbs11g; User created. SQL> grant dba to ohsdba; Grant succeeded. SQL> conn ohsdba/ohsdba Connected. SQL> create table ohs_objects as select * from dba_objects; Table created. SQL> insert into ohs_objects select * from ohs_objects; 86348 rows created. SQL> / 172696 rows created. SQL> / 345392 rows created. SQL> commit; Commit complete. SQL> SQL> create directory dpump as '/oradata'; Directory created. SQL>
[oracle@db1 ~]$ expdp ohsdba/ohsdba directory=dpump dumpfile=ohsdba_11g.dmp logfile=ohsdba.log
Export: Release 11.2.0.4.0 - Production on Sun Jun 12 11:18:35 2016 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, Data Mining and Real Application Testing options Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01": ohsdba/******** directory=dpump dumpfile=ohsdba_11g.dmp logfile=ohsdba.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 80 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/PRE_SCHEMA/PROCACT_SCHEMA >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled . . exported "OHSDBA"."OHS_OBJECTS" 66.91 MB 690784 rows Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is: /oradata/ohsdba_11g.dmp Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 11:19:10 2016 elapsed 0 00:00:29
[oracle@db1 ~]$
SQL> exec show_dumpfile_info(p_file=>'ohsdba_11g.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.: ohsdba_11g.dmp
Directory: dpump
Disk Path: /oradata
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.................: Sun Jun 12 11:19:10 2016
...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....: upgr
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 350D3A38AF2312D7E0537800000A9C09
...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.
SQL>
[oracle@db1 ~]$ ./aodu
AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 11:19:44 2016
Copyright (c) 2014, 2015, Robin.Han. All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> dmpinfo /oradata/ohsdba_11g.dmp
Dmp Filetype..................: Datapump dumpfile
Source Database Name..........: upgr
Database Job Version..........: 11.02.00.04.00
Internal Dump File Version....: 3.1
Creation Date.................: 2016-06-12 11:19:10
Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
GUID (unique job identifier)..: 350D3A38AF2312D7E0537800A9C09
Block size dump file (bytes)..: 4096
Language Name of characterset.: AL32UTF8
Characterset ID of source db..: 873
Operating System of source db.: x86_64/Linux 2.4.xx
AODU>
11g客户端导出12c数据库
在11g上创建dblink，导出12c数据库
SQL> conn ohsdba/ohsdba Connected. SQL> create database link T12c connect to ohsdba identified by ohsdba using '(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=pdb11)))'; Database link created. SQL> select * from dual@T12c; D - X SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options
[oracle@db1 ~]$ expdp ohsdba/ohsdba directory=dpump dumpfile=pdb11_12c.dmp logfile=12c.log network_link=T12c
Export: Release 11.2.0.4.0 - Production on Sun Jun 12 12:34:30 2016 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, Data Mining and Real Application Testing options Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01": ohsdba/******** directory=dpump dumpfile=pdb11_12c.dmp logfile=12c.log network_link=T12c Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OHSDBA"."OHS_OBJECTS" 82.86 MB 727408 rows Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is: /oradata/pdb11_12c.dmp Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 12:34:49 2016 elapsed 0 00:00:18
[oracle@db1 ~]$ ./aodu
AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 12:35:07 2016
Copyright (c) 2014, 2015, Robin.Han. All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> dmpinfo /oradata/pdb11_12c.dmp
Dmp Filetype..................: Datapump dumpfile
Source Database Name..........: upgr
Database Job Version..........: 11.02.00.04.00
Internal Dump File Version....: 3.1
Creation Date.................: 2016-06-12 12:34:49
Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
GUID (unique job identifier)..: 350E49AEAC812221E0537800A00F9
Block size dump file (bytes)..: 4096
Language Name of characterset.: AL32UTF8
Characterset ID of source db..: 873
Operating System of source db.: x86_64/Linux 2.4.xx
AODU>
注意：用数据泵expdp导出时，不需要设置字符集(即使你设置了，也不管用)，因为Oracle会查询数据库获取字符集的信息，然后再做导出。用network_link导出时，你看到的dump文件头部信息为你当前数据库的信息，比如数据库的名字，数据库Job版本，操作系统平台信息，字符集信息等，可以参考以上用11g客户端导出12c数据库的例子。
How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1)
