Oracle Import and Export

How to import and export data between schemas in Oracle.

Published: Friday, 20 December 2013
oracle

You will need the exp and imp utilities provided by Oracle. There are also newer corresponding “data pump” versions of these tools named expdp and impdp.

Installing imp and exp without root on linux

If you only need the imp and exp utilities, you can install this manually as a non-root user.

Download

Download the distribution zip for Oracle XE

The file I used was named oracle-xe-11.2.0-1.0.x86_64.rpm.zip. They only provide a x64 version.

unzip the distribution

$ unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

You should now have a file named Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

unpack the rpm

The rpm can only be properly installed with root permissions. If you do not have root access, unpack the file using:

$ cd Disk1/
$ rpm2cpio oracle-xe-11.2.0-1.0.x86_64.rpm | cpio -idmv

Many files will be created.

check tools were extracted

Check that the exp utility was created in ./u01/app/oracle/product/11.2.0/xe/bin

Set environment variables

Because the rpm did not have a chance to setup the environment variables using an install script, you can do this yourself each time you want to run exp

export ORACLE_HOME=/home/magicmonster/oracle/Disk1/u01/app/oracle/product/11.2.0/xe
export PATH=/home/magicmonster/oracle/Disk1/u01/app/oracle/product/11.2.0/xe/bin:$PATH
export LD_LIBRARY_PATH=/home/magicmonster/oracle/Disk1/u01/app/oracle/product/11.2.0/xe/lib:$LD_LIBRARY_PATH

In the example above I’ve assumed it is installed to /home/magicmonster/oracle. Now we are ready to use exp and imp.