如何从 dmp 文件和日志文件导入 Oracle 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6463614/
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
How to import an Oracle database from dmp file and log file?
提问by praveenb
How would I go about to create a database from a dmp-file? I do not have an existing database with the same structure on my system so it has to be complete with jobs, events, tables and so on.
我将如何从 dmp 文件创建数据库？我的系统上没有具有相同结构的现有数据库，因此它必须包含作业、事件、表等。
I placed dmp and log file in E: drive
我将 dmp 和日志文件放在 E: 驱动器中
I have tried the import utility
我已经尝试过导入实用程序
E:/>impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp
But I'm getting error as
但我收到错误
invalid argument value
bad dump file specification
unable to open dump file "E:\app\Vensi\admin\oratest\dpdump\WB_PROD_F
ULL_20MAY11.dmp" for read
unable to open file
unable to open file
(OS 2) The system cannot find the file specified.
And when I see in Windows Explorer dmp file(taken from Linux server) is showing as Crash dump file
当我在 Windows 资源管理器中看到 dmp 文件（取自 Linux 服务器）显示为崩溃转储文件时
I donot understand how I can resolve this issue. Please help me to solve this issue.
我不明白如何解决这个问题。请帮我解决这个问题。
I'm a complete newbie on Oracle...
我是 Oracle 的完全新手...
回答by Sathyajith Bhat
How was the database exported?
数据库是如何导出的？
If it was exported using
expand a full schema was exported, thenCreate the user:
create user <username> identified by <password> default tablespace <tablespacename> quota unlimited on <tablespacename>;Grant the rights:
grant connect, create session, imp_full_database to <username>;Start the import with
imp:imp <username>/<password>@<hostname> file=<filename>.dmp log=<filename>.log full=y;
If it was exported using
expdp, then start the import withimpdp:impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;
如果它是使用导出的
exp并且导出了完整的模式，那么创建用户：
create user <username> identified by <password> default tablespace <tablespacename> quota unlimited on <tablespacename>;授予权利：
grant connect, create session, imp_full_database to <username>;开始导入
imp：imp <username>/<password>@<hostname> file=<filename>.dmp log=<filename>.log full=y;
如果它是使用 导出的
expdp，则使用以下命令开始导入impdp：impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;
Looking at the error log, it seems you have not specified the directory, so Oracle tries to find the dmpfile in the default directory (i.e., E:\app\Vensi\admin\oratest\dpdump\).
查看错误日志，似乎您没有指定目录，因此Oracle 尝试dmp在默认目录（即，E:\app\Vensi\admin\oratest\dpdump\）中查找该文件。
Either move the export file to the above path or create a directory object to pointing to the path where the dmpfile is present and pass the object name to the impdpcommand above.
要么将导出文件移动到上述路径，要么创建一个指向dmp文件所在路径的目录对象，并将对象名称传递给impdp上面的命令。
回答by Ivan Laharnar mink.si
All this peace of code put into *.bat file and run all at once:
所有这些代码都放入 *.bat 文件中并立即运行：
My code for creating user in oracle. crate_drop_user.sql file
我在 oracle 中创建用户的代码。crate_drop_user.sql 文件
drop user "USER" cascade;
DROP TABLESPACE "USER";
CREATE TABLESPACE USER DATAFILE 'D:\ORA_DATA\ORA10\USER.ORA' SIZE 10M REUSE
AUTOEXTEND
ON NEXT 5M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
/
CREATE TEMPORARY TABLESPACE "USER_TEMP" TEMPFILE
'D:\ORA_DATA\ORA10\USER_TEMP.ORA' SIZE 10M REUSE AUTOEXTEND
ON NEXT 5M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
/
CREATE USER "USER" PROFILE "DEFAULT"
IDENTIFIED BY "user_password" DEFAULT TABLESPACE "USER"
TEMPORARY TABLESPACE "USER_TEMP"
/
alter user USER quota unlimited on "USER";
GRANT CREATE PROCEDURE TO "USER";
GRANT CREATE PUBLIC SYNONYM TO "USER";
GRANT CREATE SEQUENCE TO "USER";
GRANT CREATE SNAPSHOT TO "USER";
GRANT CREATE SYNONYM TO "USER";
GRANT CREATE TABLE TO "USER";
GRANT CREATE TRIGGER TO "USER";
GRANT CREATE VIEW TO "USER";
GRANT "CONNECT" TO "USER";
GRANT SELECT ANY DICTIONARY to "USER";
GRANT CREATE TYPE TO "USER";
create file import.bat and put this lines in it:
创建文件 import.bat 并将此行放入其中：
SQLPLUS SYSTEM/systempassword@ORA_alias @"crate_drop_user.SQL"
IMP SYSTEM/systempassword@ORA_alias FILE=user.DMP FROMUSER=user TOUSER=user GRANTS=Y log =user.log
Be carefull if you will import from one user to another. For example if you have user named user1 and you will import to user2 you may lost all grants , so you have to recreate it.
如果您要从一个用户导入到另一个用户，请务必小心。例如，如果您有名为 user1 的用户并且您将导入到 user2 您可能会丢失所有 grants ，因此您必须重新创建它。
Good luck, Ivan
祝你好运，伊万
回答by Marek-A-
If you are using impdpcommand example from @sathyajith-bhat response:
如果您使用来自@sathyajith-bhat 响应的impdp命令示例：
impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;
you will need to use mandatory parameter directoryand create and grant it as:
您将需要使用强制参数目录并创建并授予它：
CREATE OR REPLACE DIRECTORY DMP_DIR AS 'c:\Users\USER\Downloads';
GRANT READ, WRITE ON DIRECTORY DMP_DIR TO {USER};
or use one of defined:
或使用以下定义之一：
select * from DBA_DIRECTORIES;
My ORACLE Express 11g R2 has default named DATA_PUMP_DIR(located at {inst_dir}\app\oracle/admin/xe/dpdump/) you sill need to grant it for your user.
我的 ORACLE Express 11g R2 的默认名称为DATA_PUMP_DIR（位于 {inst_dir}\app\oracle/admin/xe/dpdump/），您仍然需要为您的用户授予它。
