Export tables from specific user in connection in sql developer

Question

I need to export all tables from specific user in oracle sql developer.

For example:

Connection: allusers (remote) User/schema: user1 Tables: table1 table2 tablen

I need to export all the tables and relationships from user1, generating an .sql or .ddl file.

After this, I'm gonna import the file in my local data base.

Connection: local user/schema: user1(imported from the file) tables: (all tables from the exported file)

How can I do that?

I was trying with data base copy, but my remote connection has not granted privileges to get data from an external user, and I can't grant privileges because I'm not the remote data base administrator.

Any idea? Thanks a lot.

Answer 1

I prefer oldfashioned export/import approach. Why? Because those utilities are designed for such things, moving things around.

For this simple example, I'm connected to a remote database (ORCL) which is 11gR2. As I'm exporting Scott's schema which doesn't contain anything special, I'm using the original EXP utility instead of Data Pump. It is simpler and creates the DMP file locally.

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>

Target database is 11gXE on my laptop. IMP utility is used to import data. I'll use SYSTEM XE user to import into a different schema (MIKE) - note FROMUSER & TOUSER parameters.

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>

Piece of cake, takes no time whatsoever. Try it.