In this article, we’ll talk about step-by-step or how to export an Oracle database, then import into a different schema name. I’m using Linux the example.
Prerequisites before Export and Import Process
- Make sure you have set variable ORACLE_HOME in your environment. Yours may be vary from mine.
export ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
- Create a directory on your system, where we gonna put the Oracle Data Pump (dump) file. As for me, I wanna save the data pump file into /home/oracle/dpump_dir
- Next we need to create the “directory” from SQLPlus. You must logged in as SYSDBA. dbora is SID name on my system.
$ORACLE_HOME/bin/sqlplus sys@dbora as sysdba
- After logged in SQLPlus, execute this command
create directory dpump_dir as '/home/oracle/dpump_dir';
grant read,write on directory dpump_dir to dbuser;
dbuser is the username that we will use it for export and import process.
- We also need to give the dbuser privileges to create any directory and to export / import full database.
grant create any directory to dbuser;
grant EXP_FULL_DATABASE to dbuser;
grant IMP_FULL_DATABASE to dbuser;
Export and Import Oracle Database
- Now for the backup / export process, type this command on your console (terminal):
expdp dbuser/dbuser_password schemas=db_schema directory=dpump_dir dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log
Don’t forget to change the value of the red variable.
- To restore / import database, type this command:
impdp dbuser/dbuser_password schemas=db_schema directory=dpump_dir dumpfile=db_dumpfile.dmp logfile=impdp_dbdump.log remap_schema=db_schema:new_db_schema remap_tablespace=source_tablespace:destination_tablespace
You can remove the remap_schema and remap_tablespace parameter, as long as the source and destination of the schema or tablespace is not changed.
That’s all, folks. Hope it helps.