August 15 2014

查看exp/expdp的dmp文件的头信息和ddl

–如果你要导入一个你根本不是知道是用exp 还是用expdp的dmp 你是要试试呢 还是用以下方法获取文件头信息呢
DECLARE
t1 ku$_dumpfile_info;
v1 NUMBER;
item_meaning VARCHAR2 (40);
v_file_type VARCHAR2 (20);
BEGIN
DBMS_DATAPUMP.GET_DUMPFILE_INFO (‘a.dmp’,
‘DMP’,
t1,
v1);
IF v1 = 1
THEN
v_file_type := ‘expdp’;
ELSE
v_file_type := ‘exp’;—- 0=unknown 1=expdp 2=exp 3=ext
END IF;
DBMS_OUTPUT.PUT_LINE (‘filetype: ‘ || v_file_type);
FOR i IN 1 .. t1.COUNT
LOOP
item_meaning :=
CASE t1 (i).item_code
WHEN 1 THEN ‘FILE_VERSION’
WHEN 2 THEN ‘MASTER_PRESENT’
WHEN 3 THEN ‘GUID’
WHEN 4 THEN ‘FILE_NUMBER’
WHEN 5 THEN ‘CHARSET_ID’
WHEN 6 THEN ‘CREATION_DATE’
WHEN 7 THEN ‘FLAGS’
WHEN 8 THEN ‘JOB_NAME’
WHEN 9 THEN ‘PLATFORM’
WHEN 10 THEN ‘INSTANCE’
WHEN 11 THEN ‘LANGUAGE’
WHEN 12 THEN ‘BLOCKSIZE’
WHEN 13 THEN ‘DIRPATH’
WHEN 14 THEN ‘METADATA_COMPRESSED’
WHEN 15 THEN ‘DB_VERSION’
WHEN 16 THEN ‘MASTER_PIECE_COUNT’
WHEN 17 THEN ‘MASTER_PIECE_NUMBER’
WHEN 18 THEN ‘DATA_COMPRESSED’
WHEN 19 THEN ‘METADATA_ENCRYPTED’
WHEN 20 THEN ‘DATA_ENCRYPTED’
ELSE ‘UNKNOWN’
END;
DBMS_OUTPUT.PUT_LINE (
RPAD (item_meaning || ‘:’, 30, ‘ ‘) || LPAD (t1 (i).VALUE, 30, ‘ ‘));
END LOOP;
END;

 

–当然 也可用strings 来查看
strings a.dmp |grep “str”|sed -e ‘s/,/,\n/g’ | MORE –str就是你想搜索的东西 什么都不写就是所有的文本
/* TEXPORT:V11.02.00
DSCOTT
RUSERS
2048*/
cat SCOTT01.DMP | head | strings |MORE —
/*
“SCOTT”.”SYS_EXPORT_SCHEMA_01″
IBMPC/WIN_NT64-9.1.0
orcl
ZHS16GBK
11.02.00.00.00
001:001:000001:000001
*/
———————————————–
–接下来我们还是用一种简单的方法 然而这种方法同时可以获取DDL

impdp ucjmh/ucjmh directory=dmp dumpfile=SCOTT01.DMP logfile=scott01.log sqlfile=scott01.sql trace=100300
–如果是Imp 那么用imp工具的INDEXFILE来获取ddl
MORE scott01.sql
/*– CONNECT UCJMH
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: SCHEMA_EXPORT/USER
— CONNECT SYSTEM
CREATE USER “SCOTT” IDENTIFIED BY VALUES ‘S:735BF47C1B7DF8AA4A82D2540870DAB28093
0D318F62A7BDD0C8C48C733F;CDC57F9E62A38D03’
DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”;

— new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO “SCOTT”;
………..
*/

data pump进程启动的时候,会有两类进程,即:Datapump Master (DM) 和 Worker (DW) processes
他们生成的trace文件产生在BACKGROUND_DUMP_DEST目录里面,命名格式如下:
– Master Process trace file: _dm_ .trc
– Worker Process trace file: _dw_ .trc



Copyright 2019. All rights reserved.

Posted 2014年8月15日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *