如何从 dmp 文件中获取 Oracle sql 文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16961295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 05:04:38 来源:igfitidea点击:
How to get an Oracle sql text from a dmp file?
提问by user2001951
Can I get an actual sql definition from an Oracle dmp file? I'm trying to migrate a complete schema into another (not oracle) type of database and I want all the tables and views etc. so I can edit them in sql format. I have a .dmp of the whole thing, but can I get it in text format?
我可以从 Oracle dmp 文件中获取实际的 sql 定义吗？我正在尝试将一个完整的架构迁移到另一种（不是 oracle）类型的数据库中，我想要所有的表和视图等，以便我可以以 sql 格式编辑它们。我有整个内容的 .dmp，但我可以以文本格式获取它吗？
回答by ExcessOperatorHeadspace
The Oracle imp command has a show parameter. From the Oracle 11.2 docs
Oracle imp 命令有一个 show 参数。来自Oracle 11.2 文档
SHOW
Default: n
When SHOW=y, the contents of the export dump file are listed to the display and not imported. The SQL statements contained in the export are displayed in the order in which Import will execute them.
The SHOW parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameter.
The impdp command has a sqlfile parameter. From the Oracle 11.2 docs
impdp 命令有一个 sqlfile 参数。来自Oracle 11.2 文档
SQLFILE
Default: There is no default
Purpose
Specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.
Syntax and Description
SQLFILE=[directory_object:]file_name
The file_name specifies where the import job will write the DDL that would be executed during the job. The SQL is not actually executed, and the target system remains unchanged. The file is written to the directory object specified in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name matching the one specified with this parameter is overwritten.
Note that passwords are not included in the SQL file. For example, if a CONNECT statement is part of the DDL that was executed, then it will be replaced by a comment with only the schema name shown. In the following example, the dashes (--) indicate that a comment follows, and the hr schema name is shown, but not the password.
-- CONNECT hr
Therefore, before you can execute the SQL file, you must edit it by removing the dashes indicating a comment and adding the password for the hr schema.
For Streams and other Oracle database options, anonymous PL/SQL blocks may appear within the SQLFILE output. They should not be executed directly.
Restrictions
If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either ALL or DATA_ONLY.
To perform a Data Pump Import to a SQL file using Oracle Automatic Storage Management (Oracle ASM), the SQLFILE parameter that you specify must include a directory object that does not use the Oracle ASM + notation. That is, the SQL file must be written to a disk file, not into the Oracle ASM storage.
The SQLFILE parameter cannot be used in conjunction with the QUERY parameter.
Example
The following is an example of using the SQLFILE parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. See "FULL".
> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql
A SQL file named expfull.sql is written to dpump_dir2.
Here is the command line for imp
这是imp的命令行
imp system@myinstance fromuser=myuser touser=user2 file=f:\mypath\mydmpfile.dmp show=y rows=n log=f:\mypath\mylogfile.log statistics=none buffer=2000000 feedback=1000
Here is some output in the .log file
这是 .log 文件中的一些输出
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by MYUSER, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing MYUSER's objects into MYNEWUSER
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'MYOLDUSER', inst_scn=>'623831137');"
"COMMIT; END;"
"CREATE DATABASE LINK "DATABASELINK1" CONNECT TO CURRENT_USER USING ''"
... and so on
"ALTER SESSION SET CURRENT_SCHEMA= "MYNEWUSER""
"CREATE TABLE "AD_HOC_TYPE" ("COLUMN_NAME" VARCHAR2(4000) NOT NULL ENABLE, ""
"AD_HOC_TYPE" NUMBER NOT NULL ENABLE, "VIEW_NAME" VARCHAR2(4000) NOT NULL EN"
"ABLE, "LOOKUP_TABLE" VARCHAR2(4000), "AD_HOC_TYPE_ID" NUMBER NOT NULL ENABL"
"E) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FRE"
"ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MYOLDTABLESPACE" LOGGI"
"NG NOCOMPRESS"
. . skipping table "AD_HOC_TYPE"
"CREATE UNIQUE INDEX "AD_HOC_TYPE_PK" ON "AD_HOC_TYPE" ("AD_HOC_TYPE_ID" ) "
"PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI"
"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MYOLDTABLESPACE" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "MYNEWUSER""
"ALTER TABLE "AD_HOC_TYPE" ADD CONSTRAINT "AD_HOC_TYPE_PK" PRIMARY KEY ("AD"
"_HOC_TYPE_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITI"
"AL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MYOLDTABLESPACE"
"OLDUSER" LOGGING ENABLE "
... and so on
"ALTER TABLE "MOA_TYPE" ENABLE CONSTRAINT "MOA_TEXT_FK""
"ALTER TABLE "MOA_TEXT" ENABLE CONSTRAINT "CHRONIC_MOA_TEXT_FK""
Import terminated successfully without warnings.
Here is the command line for impdp
这是impdp的命令行
impdp myuser@myinstance DIRECTORY=exports DUMPFILE=table.dmp sqlfile=exports:sqlddl.sql logfile=exports:impdplog.log
Here is the logfile impdplog.log contents
这是日志文件 impdplog.log 内容
;;;
Import: Release 11.2.0.3.0 - Production on Thu Jun 6 14:58:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "MYUSER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "MYUSER"."SYS_SQL_FILE_FULL_01": MYUSER/********@dultest1 DIRECTORY=exports DUMPFILE=table.dmp sqlfile=exports:sqlddl.sql logfile=exports:impdplog.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "MYUSER"."SYS_SQL_FILE_FULL_01" successfully completed at 14:58:33
Here is the SQLFILE sqlddl.sql contents
这是 SQLFILE sqlddl.sql 内容
-- CONNECT MYUSER
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 "MYUSER"."SPERRORLOG"
( "USERNAME" VARCHAR2(256 BYTE),
"TIMESTAMP" TIMESTAMP (6),
"SCRIPT" VARCHAR2(1024 BYTE),
"IDENTIFIER" VARCHAR2(256 BYTE),
"MESSAGE" CLOB,
"STATEMENT" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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"
LOB ("MESSAGE") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
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))
LOB ("STATEMENT") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
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)) ;
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'MYUSER';
t varchar2(60) := 'SPERRORLOG';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,
14,8,243,14,0,NULL,NULL,NULL,
TO_DATE('2012-12-13 22:00:14',df));
c := 'USERNAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1,1,1,14,0,3.75311197656206E+35,3.75311197656206E+35,8,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),'4848454C47454E','4848454C47454E',nv,2,nv;
c := 'TIMESTAMP';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
10,.1,10,14,0,2456121.67056713,2456270.83032407,11,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),'7870070C110626','78700C08143829',nv,2,nv;
c := 'SCRIPT';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,nv,14,0,0,0,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),nv,nv,nv,2,nv;
c := 'IDENTIFIER';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,nv,14,0,0,0,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),nv,nv,nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'MYUSER';
t varchar2(60) := 'SPERRORLOG';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'MESSAGE';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,10,4,0,0,133,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),nv,nv,nv,2,nv;
c := 'STATEMENT';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,10,4,0,0,91,0,nv,nv,
TO_DATE('2012-12-13 22:00:14',df),nv,nv,nv,2,nv;
DBMS_STATS.IMPORT_TABLE_STATS('"MYUSER"','"SPERRORLOG"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
