How to create a dump with Oracle PL/SQL Developer?

Gokul picture Gokul · May 12, 2009 · Viewed 115.8k times · Source

I need to take dump of a user (including tables, procedures ,etc.) as FILENAME.dmp.

If I create a new user and import that FILENAME.dmp, then everything should be created.

How can I create this dump file?

Don't tel me to use the Run > EXP or Run > IMP functions because, due to some problem, that feature is not working for me.

Answer

Guy picture Guy · May 12, 2009

EXP (export) and IMP (import) are the two tools you need. It's is better to try to run these on the command line and on the same machine.

It can be run from remote, you just need to setup you TNSNAMES.ORA correctly and install all the developer tools with the same version as the database. Without knowing the error message you are experiencing then I can't help you to get exp/imp to work.

The command to export a single user:

exp userid=dba/dbapassword OWNER=username DIRECT=Y FILE=filename.dmp

This will create the export dump file.

To import the dump file into a different user schema, first create the newuser in SQLPLUS:

SQL> create user newuser identified by 'password' quota unlimited users;

Then import the data:

imp userid=dba/dbapassword FILE=filename.dmp FROMUSER=username TOUSER=newusername

If there is a lot of data then investigate increasing the BUFFERS or look into expdp/impdp

Most common errors for exp and imp are setup. Check your PATH includes $ORACLE_HOME/bin, check $ORACLE_HOME is set correctly and check $ORACLE_SID is set