How to use data export utility for Oracle data utility to create dump file on local machine?

Question

The data export utility for exporting Oracle data expects the DIRECTORY (DBA_DIRECTORIES) parameter, which exists on the database server. Is it possible to map this directory to the local computer, or is there another way to export multiple tables to the local one from the oracle database?

Answer 1

You should ask yourself: “Why do I want to store data outside the database - the safest place for my data? Where is backup, restore and restore.

If you intend to move data from database A to database B, make sure that both databases have access to a common file area, where they can access datadump files through their directory object and use datapump.

If you still want to export data to the client side, you can use the good old tools exp and imp.

Answer 2

With a little hack, you can get the data pump to do what you want, but you need to have a database on your local machine.

What you need to do is create a database link on your local computer to the remote machine.

Then, in the parameters of the datapump, log into the local database as the owner of the db link, specify the option 'network_link' as the name of the database link name you created. Therefore, it should export from the remote database through the local database and create a file on your local instance. For instance:

expdp directory=<local_dir_object> network_link=<dblinkname on local instance> dumpfile=.. logfile=.. tables/schema=... 

Answer 3

When using Data Pump, there is no direct way to save the dump file on your local machine. This is how Data Pump is designed.

However, there is one possible way to achieve the desired. The workaround has two steps:

• Run expdp as usual, which creates a dump file on the server
• Use the ocp tool to transfer the dump file from the database server to the local computer (and vice versa if you want).

The ocp tool ocp for "Oracle Copy" and is written exactly for copying dump files from the database server. It is available here: https://github.com/maxsatula/ocp/releases/download/v0.1/ocp-0.1.tar.gz This is the original distribution, so after downloading and unpacking, run ./configure && make

(I hope you do not have Windows on the client side, because I never tried to compile it)

This is a simple command line tool with simple syntax. For example, this command will pull out the 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 tweak the source code and create the ocp tool for the 32-bit version of Windows:

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 that it will work with a full installation of Oracle Client (just watch for bits, there should be 32), but did not test itself.

Unfortunately, the windows ocp build ocp not have a fantastic progress indicator during file transfer. There were too many * nix-specific things in this code snippet, so I had to disable it.

In addition, since it uses the popt and zlib libraries, which are compiled as part of the GnuWin project and are only available in the 32-bit version, ocp for Windows is also 32-bit. I hope that not having a 64-bit version is not critical for you.

Update 2:

A warning! . Always check the connection to the DEDICATED server when downloading files from the server, otherwise (for the SHARED server) the downloaded copy of the file will be damaged without error messages!

Answer 4

No, the data pump sucks this path, but Oracle can get higher throughput using the same server that the db is on, so this is a compromise. Other improvements too, but I still think this is a big drawback for the data pump. To do this, use the old exp / imp tools or third-party tools.