导出 oracle 数据库（创建 .sql 数据文件）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2006139/
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
exporting oracle database (creating a .sql file of data )
提问by user223541
I have an Oracle database on one PC. I have to migrate it to my other PC. Both use separate oracle installations. Now how can I export it from my one PC and then import it into other PC?
我在一台 PC 上有一个 Oracle 数据库。我必须将它迁移到我的另一台 PC。两者都使用单独的 oracle 安装。现在如何从我的一台 PC 导出它，然后将它导入到另一台 PC？
What are the commands for exporting and then importing it?
导出然后导入的命令是什么？
I want structure as well as data to be exported and then imported. I am using Oracle db.
我想要导出然后导入结构和数据。我正在使用 Oracle 数据库。
回答by RC.
You can export the data from your source database and import it into your new one.
您可以从源数据库中导出数据并将其导入到新数据库中。
A good link demonstrating the usage of these commands can be found here
可以在此处找到演示这些命令用法的良好链接
It boils down to something like the following for exporting a full database:
导出完整数据库可归结为以下内容：
%> exp USERID=<username>/<password> FULL=Y FILE=dbExport.dmp
%> imp USERID=<username>/<password> FILE=dbExport.dmp FULL=Y
There are a multitude of options for both commands to tailor it to your needs. For example, you can restrict the import command to only import certain tables via the TABLES parameter or you can move database object between users with TOUSER, FROMUSER parameters. There are also options on whether to export or import constraints, indexes, etc. You can find all the valid parameters for both commands by executing either:
这两个命令都有多种选项可以根据您的需要进行定制。例如，您可以将导入命令限制为仅通过 TABLES 参数导入某些表，或者您可以使用 TOUSER、FROMUSER 参数在用户之间移动数据库对象。还有关于是否导出或导入约束、索引等的选项。您可以通过执行以下任一命令找到两个命令的所有有效参数：
%> exp help=Y
%> imp help=Y
回答by APC
You don't say which version of the database you are using. This is important information, because new features get added to Oracle with every release. For instance, in 10g Oracle introduced a new utility, DataPump, which replaces the older IMP and EXP (those utilities are still included in the install, they are just deprecated).
您没有说明您使用的是哪个版本的数据库。这是重要的信息，因为每个版本都会向 Oracle 添加新特性。例如，在 10g 中，Oracle 引入了一个新的实用程序 DataPump，它取代了旧的 IMP 和 EXP（这些实用程序仍然包含在安装中，只是被弃用了）。
One of the neat things about DataPump is that we can execute from inside the database as well as from an OS command line. I recently posted an example of using DataPump from PL/SQL in this SO thread. `Oracle provide comprehensive documentation.
DataPump 的优点之一是我们可以从数据库内部以及从操作系统命令行执行。我最近在这个 SO 线程中发布了一个使用来自 PL/SQL 的 DataPump 的示例。`Oracle 提供全面的文档。
edit
编辑
Neither old fashioned IMP/EXP nor DataPump generate an SQL file (*). If that is really what you want (as opposed to just porting the schema and data somehow) then things get a bit trickier. Oracle has a package DBMS_METADATAwhich we can use to generate DDL scripts, but that won't deal with the data. To generate actual INSERT statements, your best bet is to use an IDE; Quest's TOAD will do this as will Oracle's (free) SQL Developer tool. Both IDEs will also generate DDL scripts for us.
老式的 IMP/EXP 和 DataPump 都不会生成 SQL 文件 (*)。如果这真的是您想要的（而不是仅仅以某种方式移植架构和数据），那么事情就会变得有点棘手。Oracle 有一个DBMS_METADATA包，我们可以用它来生成 DDL 脚本，但它不会处理数据。要生成实际的 INSERT 语句，最好的办法是使用 IDE；Quest 的 TOAD 将执行此操作，Oracle 的（免费）SQL Developer 工具也将执行此操作。两个 IDE 也会为我们生成 DDL 脚本。
(*) A Datapump import can derive the DDL statements from a prior Datapump export file, using the SQLFILE=parameter. But that is just the structure not the data.
(*) Datapump 导入可以使用SQLFILE=参数从先前的 Datapump 导出文件中导出 DDL 语句。但这只是结构而不是数据。
