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.
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