How 2 Guru

Oracle import & export

Actually, I am a newbie for the oracle database. Never though it before I start my current job.
Lucky my company has a oracle expert & always help me to solve those problems. 

Today, just want to share some of my experience regarding the export & import for oracle.
I was taught to use the imp & exp command for import & export an oracle schema. 
Below is the imp & exp command examples:

exp command:

exp username/pass[email protected]/SID file=schema.dmp log=export.log owner=username

imp command:

imp username/[email protected]/SID file=schema.dmp fromuser=username touser=username

Recently, I received a dmp file that using the expdp command to export the schema. I tried to import the dmp into our local development environment & always fail.
After asking my colleague, he mention that we need to using the impdp command to import the database. 

Below is the expdp (Data Pump Export) & impdp (Data Pump Import) command:

expdp command:
In order to use the expdp command, we are require to create a directory object before using it. After creating the directory object, we need to grant the read & write permission for the oracle user. To execute this command, we need to login into oracle as sysdba user, after login, execute the following command.

> create directory expdp_dir as '/u01/backup/exports';
> grant read,write on directory expdp_dir to username;

After complete the grant command, we can proceed to export the database.

expdp username/[email protected]/SID DIRECTORY=expdp_dir DUMPFILE=schema.dmp FULL=y LOGFILE=expfull.log

impdp command:
For the import command, it is almost the same with the export. We still need to create the directory object & grant the read & write permission for the user. After complete it, may proceed to import the schema. 

impdp username/[email protected]/SID DIRECTORY=expdp_dir DUMPFILE=schema.DMP LOGFILE=import.log

But you might facing some problem while the schema name is different for the production & development (Above impdp command will use the same schema name as dmp file). Below is another impdp command to rename the schema name while import.

impdp username1/password@stats02 DIRECTORY=expdp_dir DUMPFILE=schema.DMP LOGFILE=import.log SCHEMAS=username REMAP_SCHEMA=username:username1