Oracle impdp – Importing file from remote server

impdporacle

Am I missing something or is it not possible to import a dump (.dmp) file using impdp to a database on another server other than where it was created? All of my investigations lead me to believe that this cannot be done…that the dump file needs to reside locally on the server where the data is to be imported or NFS mounted so it appears to be local. This seems to be a capability that the old "exp/imp" utilities used to have, but no longer exist. I know you can move data using impdp and the REMOTE_LINK option but in order to use this, the data must physically reside in a schema within the database instance on the remote side for it to be copied. It can't reside in a dump/exported file.

For example. I have Server "A" and Server "B". Each of them with an Oracle instance on it. On "A", I perform an export using expdp of schema "TESTDATA" to a dumpfile named "testdata.dmp" where it is store on "A". At some point in the future I would like to restore the contents of the "TESTDATA" dump file (testdata.dmp) to a new schema (TESTDATA2) on server "B".

At this point, is it true that my only options are to:

  1. Copy testdata.dmp to server "B" and perform an import directly on server "B"
  2. NFS mount the directory containing testdata.dmp on server "A" from server "B" so the dmp file appears local to server "B" and then perform the import.
  3. Create a temporary schema (TMPSCHEMA) on server "A", import the test.dmp file to the temporary schema using the REMAP_SCHEMA option in impdp, and then perform an impdp on server "B" using the REMOTE_LINK option pulling the data from TMPSCHEMA on "A"

Either I'm missing something here, or Oracle left a huge gap in functionality between impdp and imp.

Best Answer

From Oracle Data Pump utilities:

Dump files are read and written directly by the server and, therefore, do not require any data movement to the client.

impdp/expdp unlike imp/exp does not move the data. They only invoke DBMS_DATAPUMP package and actual data movement is done by the Oracle instance. So data pump can access external data the same way as all other Oracle procedures - loading files via directory object or SELECT data via dblink.

Related Question