CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11gdatapump
GIS_DBA
GIS_DBA

Reputation: 221

Export a table from one database to another database on another server

I want to export a table from a database located on one server to another database located on another server.

Here is my setup:

  1. Oracle 11g database: [email protected]
  2. Oracle 11g XE: [email protected]

The command that I found is used to export/import table within the same database:

expdp user/password dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=
hr_exp.log

impdp user/password dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=hr_imp.log

Any idea or tips on how to solve this?

Upvotes: 1

Views: 2449

Answers (2)

Dhimant Patel
Dhimant Patel

Reputation: 33

Just to complement above answer, you can also use network_link option on the target server. This will not require any directory access and will allow to do import on the target sever via link.

The link should be pointing to the source database.

On TARGET DB (Data would be imported in this db)

connect user/password@target
CREATE DATABASE LINK sourceHR CONNECT TO hr IDENTIFIED BY hrpassword USING 'tns2source';
impdp user/password@target network_link=sourceHR tables=<table1>...

If you omit tables, it would be a schema level import for all objects on the source schema.

Hope it helps!

Upvotes: 1

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

Datapump is a server-side tool only, which means you have to make the dumpfile accessible for serverB to import.

  • Do your expdp on serverA
  • Use scp (linux) or copy (win) to transfer the dumpfile to ServerB
  • impdp on ServerB

Another option is to have your directory-object point to a common area accessible from both serverA and serverB.

Upvotes: 1

Related Questions