2

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.

Improve this question
5
  • imp couldn't do it? I think you're wrong. Feel free to prove otherwise! What it could do, was import from a pipe (|) or named pipe (which is a fake file which buffers data), so I imagine you would have been using other utilities along with imp, rather than imp supporting such functionality directly.
    – Philᵀᴹ
    Commented Aug 13, 2013 at 17:09
  • I swear I could do it...something like: imp scott/tiger@remotedb mydumpfile.dmp mydumpfile.dmp is local and the scott/tiger account is on the server defined by "remotedb".
    – GregH
    Commented Aug 13, 2013 at 19:19
  • You remember right: docs.oracle.com/cd/E11882_01/server.112/e22490/…
    – Mindaugas Riauba
    Commented Aug 13, 2013 at 19:29
  • Always happy to be proved wrong!
    – Philᵀᴹ
    Commented Aug 14, 2013 at 23:16
  • @Philᵀᴹ - Almost 10 years too late - the problem with the old imp and exp is that they can't handle XMLType tables. Apart from that I was quite happy to continue to not use impdp and expdp.
    – DAB
    Commented Jun 23, 2023 at 7:42

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • So my statements are correct? You can't import a dump file into a remote database instance?
    – GregH
    Commented Aug 13, 2013 at 19:19
  • Yes you are correct. Also expdp cannot write to a pipe so it is not possible to script something like write to pipe, netcat it to remote machine and import here.
    – Mindaugas Riauba
    Commented Aug 13, 2013 at 19:36
0

There is an option to perform a datapump import by sourcing the data from another database via a database link, which gets you closer to a solution under some circumstances, particularly if you can use flashback on the source database to export as of some time in the recent past. http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#CJABHJHD

That aside, while you can't source the remote files directly using datapump, you can use DBMS_File_Transfer or DBMS_Scheduler to move them from one db server to another: http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles008.htm

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.