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
One Comment to “Oracle import & export”
Leave a Reply
Shit