如何最好地重新创建Oracle数据库? [英] How best can I recreate an Oracle database?
问题描述
Oracle 11gR2(x86 Windows):
Oracle 11gR2 (x86 Windows):
我有一个包含250个具有索引和约束的表的数据库.我需要在新的数据库中重新创建这些表，索引和约束并加载数据.我需要知道如何在SQL Plus和/或SQL Developer中执行以下操作，除非有一个可以自动完成所有操作的神奇实用程序.预先感谢！
I have a db with 250 tables with indexes and constraints. I need to re-create these tables, indexes and constraints in a new db and load the data. I need to know how to do the following in SQL Plus and/or SQL Developer, unless there's a magical utility that can automate all of this. Thanks in advance!
-
从250个表中卸载(导出)所有数据.
Unload (export) all the data from the 250 tables.
为250个表创建一个包含CREATE TABLE语句的sql脚本文件.
Create an sql script file containing the CREATE TABLE statements for the 250 tables.
为250个表创建一个包含CREATE INDEX语句的sql脚本文件.
Create an sql script file containing the CREATE INDEX statements for the 250 tables.
为250个表创建一个包含ALTER TABLE ADD CONSTRAINT语句的sql脚本文件.
Create an sql script file containing the ALTER TABLE ADD CONSTRAINT statements for the 250 tables.
运行脚本以在新数据库中创建表.
Run the script to create the tables in a new db.
将导出的数据加载到新数据库的表中.
Load the exported data into the tables in the new db.
运行脚本以创建所有索引.
Run the script to create all the indexes.
运行脚本以添加所有约束.
Run the script to add all the contraints.
我已连接到远程桌面，该桌面链接到Windows Server 2008上的源数据库.远程仅安装了Oracle客户端.出于安全原因，不允许我直接从本地计算机链接到Win Server，因此我可以将整个源数据库转储到远程数据库，然后将其压缩到本地目标计算机吗?我正在尝试在计算机上复制整个数据库.
I'm connected to the remote desktop which links to the source db on a Windows Server 2008. The remote only has an Oracle client installed. For security reasons, I'm not allowed to link directly from my local computer to the Win Server, so can I dump the whole source db to the remote then zip it to my local target machine? I'm trying to replicate the entire db on my computer.
推荐答案
从Oracle 10g开始，您可以使用 (从Oracle文档中引用)
Starting from Oracle 10g, you could use the Data Pump command-line clients expdb and impdb to export/import data and/or schema from one DB to an other. As a matter of fact, those two command-line utilities are only wrappers that "use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line." (quoted from Oracle's documentation)
根据需要，您将必须创建一个目录，然后使用expdb生成数据库的完整转储:
Given your needs, you will have to create a directory then generate a full dump of your database using 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
由于转储是以某种二进制格式编写的，因此您将必须使用相应的导入实用程序来(重新)导入数据库.基本上在上述命令中用impdb替换expdb:
As the dump is written using some binary format, you will have to use the corresponding import utility to (re)import your DB. Basically replacing expdb by impdb in the above command:
sh$ impdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log
对于简单的表转储，请改用该版本:
For simple table dump, use that version instead:
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;)，就可以将其与标准用户帐户一起使用.
As you noticed, you can use it with your standard user account, provided you have access to the given directory (GRANT READ, WRITE ON DIRECTORY dump_dir TO sylvain;).
有关详细的用法说明，请参见
For detailed usage explanations, see
这篇关于如何最好地重新创建Oracle数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
