database - 从 sql developer 中连接的特定用户导出表

Question

我需要从 oracle sql developer 中的特定用户导出所有表。

例如：

连接：allusers（远程）用户/模式：user1 表：table1 table2 tablen

我需要从 user1 导出所有表和关系，生成一个 .sql 或 .ddl 文件。

在此之后，我将在本地数据库中导入文件。

连接：本地用户/模式：用户1（从文件中导入）表：（从导出文件中的所有表）

我怎样才能做到这一点？

我正在尝试使用数据库副本，但我的远程连接没有授予从外部用户获取数据的权限，并且我无法授予权限，因为我不是远程数据库管理员。

任何想法？非常感谢。

标签： databaseoracleexportoracle-sqldeveloper

Answer 1

我更喜欢老式的导出/导入方法。为什么？因为这些实用程序是为这样的事情而设计的，可以移动东西。

对于这个简单的示例，我连接到一个远程数据库 (ORCL)，它是 11gR2。由于我正在导出不包含任何特殊内容的 Scott 模式，因此我使用原始EXP 实用程序而不是 Data Pump。它更简单，并在本地创建 DMP 文件。

c:\Temp>exp scott/tiger@orcl file=scott_remote.dmp

Export: Release 11.2.0.2.0 - Production on ╚et Pro 27 21:01:50 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                      EMPLOYEES          1 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                           TEST          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

c:\Temp>

我的笔记本电脑上的目标数据库是 11gXE。IMP 实用程序用于导入数据。我将使用 SYSTEM XE 用户导入不同的模式 (MIKE) - 注释FROMUSER和TOUSER参数。

c:\Temp>imp system/pwd@xe file=scott_remote.dmp fromuser=scott touser=mike

Import: Release 11.2.0.2.0 - Production on ╚et Pro 27 21:14:58 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into MIKE
. . importing table                        "BONUS"          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                    "EMPLOYEES"          1 rows imported
. . importing table                     "SALGRADE"          5 rows imported
. . importing table                         "TEST"          1 rows imported
About to enable constraints...
Import terminated successfully without warnings.

c:\Temp>

小菜一碟，一点时间都没有。试试看。