对于使用exp/expdp导出的dmp文件，可以使用以下几种方式取得其metadata或DDL。
1 获取Expdp dumpfile中的DDL
可以使用impdp的sqlfile选项，将其中的DDL尽数倒入该选项指定的文件中，此过程并不会真正执行impdp操作。
-- create an export Data Pump dumpfile:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott
-- create a script file of this Data Pump dumpfile with all DDL statements:
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
导出的sql格式如下，须做部分调整方可使用
-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE;
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625');
COMMIT;
END;
/
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
2 获取exp dumpfile中的DDL
可使用imp的show选项，与impdp的sqlfile类似
-- create an export dumpfile with the classic export client:
% exp system/manager FILE=exp_s.dmp LOG=exp_s.log OWNER=scott
-- create a logfile of this dumpfile with all DDL statements:
% imp system/manager FILE=exp_s.dmp LOG=imp_show.log FULL=y SHOW=y
导出的DDL log格式如下
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing SCOTT's objects into SCOTT
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301"
"131');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"
"SERS" LOGGING NOCOMPRESS"
. . skipping table "BONUS"
...
Import terminated successfully without warnings.
3 查看dumpfile的internal header
每个 dumpfile都有一个header block存储metadata，从10.2.0.1开始可以使用dbms_datapump.get_dumpfile_info读取访问，
输出结果依oracle版本而异，procedure代码如下：
CREATE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dumpfile can be found
-- p_file = simple filename of export dumpfile (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(22) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Show generic info:
-- ==================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 19-MAR-2008');
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(22);
FOR i in 1 .. 22 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;
-- Show dumpfile 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_ENCPWD_MODE CONSTANT NUMBER := 22;
-- 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;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype = 1 OR v_filetype = 2 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.: ' || var_values(5));
dbms_output.put_line('...Direct Path.....: ' || var_values(13));
dbms_output.put_line('...Export Version..: ' || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
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)',
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 (Dual)',
'5', '5 (Transparent)',
var_values(22)) INTO var_values(22) FROM dual;
dbms_output.put_line('...File Version....: ' || var_values(1));
dbms_output.put_line('...Master Present..: ' || var_values(2));
dbms_output.put_line('...GUID............: ' || var_values(3));
dbms_output.put_line('...File Number.....: ' || var_values(4));
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Creation Date...: ' || var_values(6));
dbms_output.put_line('...Flags...........: ' || var_values(7));
dbms_output.put_line('...Job Name........: ' || var_values(8));
dbms_output.put_line('...Platform........: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance........: ' || var_values(10));
END IF;
dbms_output.put_line('...Language........: ' || var_values(11));
dbms_output.put_line('...Block size......: ' || var_values(12));
dbms_output.put_line('...Metadata Compres: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed.: ' || var_values(18));
dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
dbms_output.put_line('...Column Encrypted: ' || var_values(21));
dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
dbms_output.put_line('...Master Piece Num: ' || var_values(17));
END IF;
END IF;
IF v_fileversion >= '1.1' THEN
dbms_output.put_line('...Job Version.....: ' || var_values(15));
END IF;
dbms_output.put_line('...Max Items Code..: ' ||
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;
/
11g输出结果如下
-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
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.: expdp_s.dmp
Directory: my_dir
Disk Path: /bugmnt7/em/celclnx7/user/expdp/work
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 2.1 (Oracle11g Release 1: 11.1.0.x)
...Master Present..: 1 (Yes)
...GUID............: AE9D4A8A85C6444F813600C00199745A
...File Number.....: 1
...Characterset ID.: 46 (WE8ISO8859P15)
...Creation Date...: Wed Mar 19 16:06:45 2008
...Flags...........: 2
...Job Name........: "SYSTEM"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: ORCL
...Language........: WE8ISO8859P15
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.01.00.00.00
...Max Items Code..: 20
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Most items are self-explanatory. Some additional details for specific items:
GUID: this value uniquely identifies an Export Data Pump dumpfile (set).
Flags: used internally.
Block Size: standard dumpfile header size is 4 kb (cannot be modified).
Direct Path: only used in a dumpfile created with the classic export client.
对于datapump dumpfile，还可使用如下方法
-- create a SQL file with TRACE parameter value 100300 (trace file layer);
-- although this impdp job will give an error (ORA-39166: Object ... was not found)
-- a trace file will be written with the file header details we are interested in:
D:\Work\Expdp> impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s%U.dmp
NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300
以上命令会在BACKGROUND_DUMP_DEST目录下生成[SID]dm[number]_[PID].trc
对于更早版本的dumpfile，可以使用如下方法替代
% cat exp_s.dmp | head | strings
.EXPORT:V10.02.01
DSYSTEM
RUSERS
8192
Thu Dec 13 13:36:26 2007exp_s.dmp
#G#G
#G#G
+00:00
BYTE
参照文档
How to Gather the Header Information and the Content of an Export Dumpfile ? [ID 462488.1]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-732940/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-732940/
9717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
