expdp creating file on my local machine?



A common question I see is why can't datapump create a dumpfile on 'my local pc' or on some other server in the same wap that exp used to?

expdp (datapump) was totally changed from the old exp utility and is now a 'server based' utility and runs everything from within the database to the database server. expdp has a number of benefits (performance,flexibility,resumability etc) but the one thing it won't now do is create files on wherever the exp utility is run from)

However there is one exception to the rule that it won;t create files on your 'local' server - and thats if you are running a database on your local server - if you are you can use this as a 'pass through' and the data can basically be shipped from the remote database to a local database using network link and then dumped out to a 'local' file on your 'local' server.

Most times you would want to load the data directly into the other database via the network link but the option is there to just use the 'pass through' - here's how its done:

1) set up a database link to the 'source' system.

sqlplus> create public database link test connect to x identified by x using 'test.WORLD';

2) run the export (as youo can see from the error it is creating a file on the local server)

expdp pumpy/pumpy network_link=test content=metadata_only dumpfile=test.dmp

Export: Release 11.2.0.3.0 - Production on Mon May 13 13:27:25 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

With the Partitioning option

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "/oracle/11.2.0.3.0.DB/rdbms/log/test.dmp"

ORA-27038: created file already exists

Additional information: 1

3) remove the existing file


rm "/oracle/11.2.0.3.0.DB/rdbms/log/test.dmp"

4) re-run and now works fine

expdp pumpy/pumpy network_link=test content=metadata_only dumpfile=test.dmp

Export: Release 11.2.0.3.0 - Production on Mon May 13 13:27:36 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

With the Partitioning option

Starting "PUMPY"."SYS_EXPORT_SCHEMA_04":  pumpy/******** network_link=eipop cont

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Master table "PUMPY"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded

******************************************************************************

Dump file set for PUMPY.SYS_EXPORT_SCHEMA_04 is:

  /oracle/11.2.0.3.0.DB/rdbms/log/test.dmp

Job "PUMPY"."SYS_EXPORT_SCHEMA_04" successfully completed at 13:28:10

2 comments:

  1. If you use network_link then the expdp will become extremely slow.

    ReplyDelete
  2. It will be slower yes - but it depends on your requirements. How else are you going to get the file on your local machine? datapump to the local db server and then ftp/scp it to your local machine - will that be any faster - or any easier?

    ReplyDelete