How to use Oracle data pump export utility to create dump file in local machine?

Question

Oracle data pump export utility expect a parameter DIRECTORY (DBA_DIRECTORIES) which exist in DB server. Is it possible to map this directory to local machine or is there any other way to export multiple table to local from oracle database?

Answer 1

No, data pump sucks that way, but Oracle can get faster throughput using the same server the db sits on, so thats the tradeoff. Other enhancements too, but I still think this is a big disadvantage for data pump. Use old exp/imp or third party tools for this purpose.

Answer 2

If using Data Pump, there is no direct way to store a dump file on your local machine. That is the way how Data Pump designed.

However, there is one of possible ways to achieve what you want. A workaround has two steps:

1. Run expdp as usual, which creates a dump file on server
2. Use ocp tool to transfer a dump file from a database server to your local machine (and back, if you want to).

An ocp tool stands for "Oracle Copy" and written exactly for the purpose of copying dump files back and forth from/to a database server. It is available here: https://github.com/maxsatula/ocp/releases/download/v0.1/ocp-0.1.tar.gz That is a source distribution, so once downloaded and unpacked, run ./configure && make

(Hopefully you do not have Windows on a client side, because I never tried to compile it there)

That is a simple command-line tool with a simple syntax. For example, this command will pull a file for you:

ocp <connection_string> DATA_PUMP_DIR:remote_file_name.dmp local_file_name.dmp

The tool uses a database connection and a minimum set of database privileges.

Update:

Finally I was able to adjust the source code and build ocp tool for Windows 32-bit:

https://github.com/maxsatula/ocp/releases/download/v0.1/ocp-0.1-win32.zip

Compiled/tested with 32-bit Instant Client 11.2.0.4 available here: http://www.oracle.com/technetwork/topics/winsoft-085727.html

instantclient-basiclite-nt-11.2.0.4.0.zip (20,258,449 bytes)

I believe it will work with a full Oracle Client installation too (just watch for bits, should be 32), however did not check myself.

Unfortunately, Windows build of ocp does not have a fancy progress meter during file transfer. That piece of code had too much *nix-specific stuff, so I had to cut it off.

Also, since it uses popt and zlib libraries, which are compiled as a part of GnuWin project, and available in 32-bit only, ocp for Windows is 32-bit only too. Hopefully, not having of a 64-bit version is not mission critical to you.

Update 2:

Warning! Make sure you always use DEDICATED server connection when download files from server, otherwise (for SHARED server) the downloaded copy of the file will be corrupted with no error messages!