Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置： 首页 » 技术文章 » 备份恢复

如何查看expdp/exp dmp文件信息

时间: 2016-06-12 13:16:15  | 作者: ohsdba  | 
如非注明，本站文章皆为原创。欢迎转载，转载时请注明出处和作者信息。

每个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数据库的例子。 




	



Reference 


	How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1)



 


		

				

			关键词:dmp expdp 		

		
相关文章

		expdp on physical standby
aodu(At Oracle Database Utility)之dmpinfo
如何查看expdp/exp dmp文件信息
Unix/Windows平台数据泵备份常用脚本
Oracle DataPump
			
	

	

		

			
			
推荐文章

			
		

		

			
联系我们

			

	

  




	Oracle Software Download Index

 




	Oracle Database Upgrade 




	Oracle SQLMaria 




	Oracle Database Document 




	Oracle Cloud Document 




	Oracle Cloud中文链接 




	   




	eDelivery

 




	GoldenGate

 


My Oracle Support 



	Exadata Document 




	Oracle Linux 




	Oracle Fusion Middleware 




	MySQL Document 




	PostgreSQL

 




	Python

 




	Apache

 




	Github 




	Sourceforge 




	CNCF 




	Ruby 




	Go 




	ipcheck 




	ipaddressguide 




	db-engines




	Linux Kernel


		

	




Top