代码之家 › 专栏 › 技术社区 › Csanchez

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

oracle-sqldeveloper export oracle database

Csanchez · 技术社区 · 6 年前

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

例如:

连接:Allusers(远程) 用户/架构:user1 表:表1 表2 塔布伦

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

之后,我会把文件导入本地数据库。

连接:本地用户/架构:user1(从文件导入) tables:(导出文件中的所有表)

我怎样才能做到呢?

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

有什么想法吗? 谢谢。

1 回复 | 直到 6 年前

Littlefoot 6 年前

我更喜欢 老式的 出口/进口方法。为什么?因为这些工具是为这些东西设计的,移动东西周围。

对于这个简单的例子,我连接到一个远程数据库(orcl),它是11gr2。在导出不包含任何特殊内容的scott模式时,我使用 起初的 exp实用程序而不是数据泵。它更简单,并在本地创建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 user导入另一个模式(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>

小菜一碟,不用花时间。试试看。