如何将Oracle中的数据库模式导出到转储文件 [英] How to export database schema in Oracle to a dump file

问题描述

我在Oracle机器上安装了Oracle服务器上的数据库模式。我需要在转储文件中导出架构相关信息。

如何做？

解决方案

这取决于哪个版本的Oracle？旧版本需要exp（导出），较新版本使用expdp（数据泵）; exp被弃用，但仍然可以工作大部分时间。

在开始之前，请注意，数据泵将导出到服务器端Oracle目录，这是一个Oracle符号位置在数据库中映射到物理位置。可能有一个默认目录（DATA_PUMP_DIR），通过查询DBA_DIRECTORIES进行检查：

  SQL>从dba_directories中选择* 
  

...如果没有，创建一个

  SQL>创建目录DATA_PUMP_DIR为'/ oracle / dumps'; 
 SQL>授予myuser目录DATA_PUMP_DIR全部; -  DBA不需要这个授权
  

假设您可以作为SYSTEM用户或另一个DBA连接，可以将任何模式导出到默认目录中：

  $ expdp system / manager schemas = user1 dumpfile = user1.dpdmp 
  

或指定特定目录，添加 directory =< directory name> ：

  C：\> expdp system / manager schemas = user1 dumpfile = user1.dpdmp directory = DUMPDIR 
  

对于较旧的导出实用程序，您可以导出到您的工作目录，甚至可以在远离服务器的客户机上使用：

  $ exp系统/ manager owner = user1 file = user1.dmp 
  

确保导出以正确的字符集完成。如果您没有设置环境，Oracle客户端字符集可能与DB字符集不匹配，Oracle将执行字符集转换，这可能不是您想要的。您会看到一个警告，如果是这样，那么在设置NLS_LANG环境变量后，您将要重复导出，以便客户端字符集与数据库字符集匹配。这将导致Oracle跳过字符集转换。

美国UTF8（UNIX）的示例：

  $ export NLS_LANG = AMERICAN_AMERICA.AL32UTF8 
  

Windows使用SET，使用日语UTF8 ：

  C：\>设置NLS_LANG = Japanese_Japan.AL32UTF8 
  

有关数据泵的更多信息，请参见： http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#g1022624

I have a database schema on Oracle server installed on a Linux machine. I need to export the schema related information in a dump file.

How to do this ?

解决方案

It depends on which version of Oracle? Older versions require exp (export), newer versions use expdp (data pump); exp was deprecated but still works most of the time.

Before starting, note that Data Pump exports to the server-side Oracle "directory", which is an Oracle symbolic location mapped in the database to a physical location. There may be a default directory (DATA_PUMP_DIR), check by querying DBA_DIRECTORIES:

  SQL> select * from dba_directories;

... and if not, create one

  SQL> create directory DATA_PUMP_DIR as '/oracle/dumps';
  SQL> grant all on directory DATA_PUMP_DIR to myuser;    -- DBAs dont need this grant

Assuming you can connect as the SYSTEM user, or another DBA, you can export any schema like so, to the default directory:

 $ expdp system/manager schemas=user1 dumpfile=user1.dpdmp

Or specifying a specific directory, add directory=<directory name>:

 C:\> expdp system/manager schemas=user1 dumpfile=user1.dpdmp directory=DUMPDIR

With older export utility, you can export to your working directory, and even on a client machine that is remote from the server, using:

 $ exp system/manager owner=user1 file=user1.dmp

Make sure the export is done in the correct charset. If you haven't setup your environment, the Oracle client charset may not match the DB charset, and Oracle will do charset conversion, which may not be what you want. You'll see a warning, if so, then you'll want to repeat the export after setting NLS_LANG environment variable so the client charset matches the database charset. This will cause Oracle to skip charset conversion.

Example for American UTF8 (UNIX):

 $ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Windows uses SET, example using Japanese UTF8:

 C:\> set NLS_LANG=Japanese_Japan.AL32UTF8

More info on Data Pump here: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#g1022624

这篇关于如何将Oracle中的数据库模式导出到转储文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！