如何最好地重新创建Oracle数据库？

Question

Oracle 11gR2 (x86 Windows):

我有一个带有索引和约束的250个表的数据库。我需要在新数据库中重新创建这些表、索引和约束，并加载数据。我需要知道如何在SQL Plus和/或SQL Developer中执行以下操作，除非有一个神奇的工具可以自动完成所有这些操作。先行感谢！

1. 从250个表中卸载（导出）所有数据。

2. 创建一个sql脚本文件，其中包含250个表的CREATE TABLE语句。

3. 创建一个sql脚本文件，其中包含250个表的CREATE INDEX语句。

4. 创建一个sql脚本文件，其中包含250个表的ALTER TABLE ADD CONSTRAINT语句。

5. 运行脚本以在新数据库中创建表。

6. 将导出的数据加载到新数据库中的表中。

7. 运行脚本以创建所有索引。

8. 运行脚本以添加所有约束。

编辑：我连接到远程桌面，该桌面链接到Windows Server 2008上的源数据库。远程桌面只安装了Oracle客户端。出于安全原因，我不允许直接从我的本地计算机链接到Win Server，所以我可以将整个源数据库转储到远程，然后将其压缩到我的本地目标机器吗？我正在尝试在我的计算机上复制整个数据库。

- FrankRuperto

Answer 1

从Oracle 10g开始，你可以使用Data Pump命令行客户端expdb和impdb来将数据和/或模式从一个数据库导出/导入到另一个数据库。实际上，这两个命令行工具只是包装器，"使用在DBMS_DATAPUMP PL/SQL包中提供的过程来执行导出和导入命令，并使用在命令行输入的参数。"_{(引用自Oracle文档)} 根据您的需求，您需要创建一个目录，然后使用expdb生成数据库的完整转储：

SQL> CREATE OR REPLACE DIRECTORY dump_dir AS '/path/to/dump/folder/';

sh$ expdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log

由于转储文件是使用二进制格式编写的，因此您需要使用相应的导入实用程序来重新导入数据库。基本上，在上面的命令中将expdb替换为impdb：

sh$ impdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log

---

对于简单的表格转储，请使用该版本：

sh$ expdp sylvain@db10g tables=DEPT,EMP directory=DUMP_DIR dumpfile=db.dmp logfile=db.log

正如您注意到的那样，只要您有访问给定目录的权限 (GRANT READ, WRITE ON DIRECTORY dump_dir TO sylvain;)，您就可以使用标准用户帐户。

---

有关详细使用说明，请参见

• http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php。

- Sylvain Leroux

---

还有一个因素：源数据库位于远程系统上，我需要在本地机器上重新创建该数据库。我能否只保存整个\oradata文件夹到Dropbox中，并用它替换我的\oradata？ - FrankRuperto

@Frank 只是移动/oradata文件夹吗？我不能确定，我从未这样做过。但如果您可以访问该文件夹，仍然可以使用expdb将数据库转储到其中，并仅复制转储文件到您的系统，以便使用impdb重新导入。对我来说更安全。 - Sylvain Leroux

我刚刚使用了Oracle Data Modeler的“生成DDL”选项，为创建表、索引和PK/FK约束创建了单独的SQL脚本文件。唯一的问题是它为每个表创建了单独的脚本文件，而不是一个用于创建所有表的脚本文件，一个用于创建所有索引的脚本文件，以及一个用于所有约束的脚本文件:( 我正在Winx64上工作，所以我可以将这些文件连接成一个文件。 - FrankRuperto

请注意，源数据库和目标数据库都在Windows平台上，因此除了在目录路径中使用反斜杠之外，我假设您在Unix shell示例中使用的expdp / impdb命令对于DOS cmd shell保持不变。我尝试在基于浏览器的数据库控制实用程序中使用datapump，但无法使其正常工作。 - FrankRuperto

@Frank，你应该认真研究expdb的文档。它可以实现你想要的任何功能。对于简单的表格转储，我认为你不需要高级权限——你的用户账户可能就足够了。 - Sylvain Leroux

Answer 2

如果您可以从本地数据库创建到当前包含数据的数据库的数据库链接，那么您可以使用DBMS_DATAPUMP包来复制整个模式。这是一个可从数据库内部调用的Datapump接口（如@Sylvain Leroux所提到的）。

DECLARE
   dph NUMBER;
   source_schema VARCHAR2 (30) := 'SCHEMA_TO_EXPORT';
   target_schema VARCHAR2 (30) := 'SCHEMA_TO_IMPORT';
   job_name VARCHAR2 (30) := UPPER ('IMPORT_' || target_schema);
   p_parallel NUMBER := 3;
   v_start TIMESTAMP := SYSTIMESTAMP;
   v_state VARCHAR2 (30);
BEGIN
   dph :=
      DBMS_DATAPUMP.open ('IMPORT',
                          'SCHEMA',
                          'DB_LINK_NAME',
                          job_name);
   DBMS_OUTPUT.put_line ('dph = ' || dph);
   DBMS_DATAPUMP.metadata_filter (dph,
                                  'SCHEMA_LIST',
                                  '''' || source_schema || '''');
   DBMS_DATAPUMP.metadata_remap (dph,
                                 'REMAP_SCHEMA',
                                 source_schema,
                                 target_schema);
   DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
   DBMS_DATAPUMP.set_parallel (dph, p_parallel);
   DBMS_DATAPUMP.start_job (dph);
   DBMS_DATAPUMP.wait_for_job (dph, v_state);
   DBMS_OUTPUT.put_line ('Export/Import time: ' || (SYSTIMESTAMP - v_start));
   DBMS_OUTPUT.put_line ('Final state: ' || v_state);
END;

上面的脚本实际上是复制并重命名模式。如果您想保持相同的模式名称，我认为您只需要删除metadata_remap调用。

- Allan

Answer 3

SQL开发人员可以通过创建带有格式化查询结果的INSERT语句来帮助解决#1：

Select /*insert*/ * 
from My_Table;

- Steve

---

我不知道这适用于哪个数据库，但肯定不是Oracle。 - Allan

@Allan 这是 SQL Developer 的一个特定功能，是问题中提到的工具之一。虽然这是一个很好的功能，但我怀疑它的效果不如数据泵，并且它只解决了其中的一个问题。 - Jon Heller