Using Oracle Import and Export
Summary
This is a description on how to use Oracle’s exp
and imp
utilities to move tables between databases. It transfers the data over the network, which avoids the requirement to store an intermediate file and also neatly side-steps the Linux 32Gb file limit for big databases. It’s a good idea to run the Oracle commands in a screen session.
Create a database account on the destination host which matches the name of the account on the source host
drop user owner cascade
create user owner identified by password
grant connect, resource, create view to owner
Allow no-password ssh logins from the source host to the destination host
As the oracle
user on the source database host:
- Run
ssh-keygen
on the source host. Don’t specify a passphrase.
$ ssh-keygen -t rsa
- Copy the public key you just generated to the destination machine:
$ ssh-copy-id oracle@dest-host
Set up a named pipe on the destination host
Logged in a oracle
on the destination host:
$ mknod /tmp/inpipe.dmp p
Set oracle imp
to read data from that pipe
Destination host:
$ imp system/password@orcl fromuser=ppowner touser=ppowner file=/tmp/inpipe.dmp &
Set up a named pipe on the source host
Source host:
$ mknod /tmp/outpipe.dmp p
Allow the pipe to be tunnelled over ssh
Soure host:
ssh -C oracle@dest-host 'cat >/tmp/inpipe.dmp' < /tmp/outpipe.dmp &
Set oracle exp to write to the named pipe
Source host:
$ exp system/password@orcl owner=ppowner file=/tmp/outpipe.dmp
You should expect to see output from exp
and imp
on both hosts. The export process may finish before the import; that’s OK, just leave the import to finish in its own sweet time.
Exporting and Importing individual database users
Run the following command to export the data for the user owner
, to the owner.dmp
file:
exp userid=owner/owner grants=y indexes=y file=owner.dmp
For each user, the username, password and the export dump file should be changed accordingly.
Do the following to import all the data for a user:
- Make sure the user being imported exists in the database, with ownership to the same table spaces as before (when the user data was exported).
- Run the following command to import the data. It’s virtually the same command which exported the data. The
exp
command is replaced with theimp
command. The example below imports all the data belonging to theowner
user into the new database.
$ imp userid=owner/owner grants=y indexes=y statistics=recalculate file=owner.dmp
A caveat: imp
and exp
can cause stale statistics to be imported.
During functional and performance testing, I regularly use Oracle imp
and exp
to backup and restore database snapshots. If you use these tools, you need to be aware that the restored snapshot may exhibit different SQL performance characteristics than the original database.
The effect on performance has to do with re-importing questionable statistics: From http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96525/expus.htm
EXP-00091 Exporting questionable statistics
Cause: Export was able to export statistics, but the statistics may not be useable. The statistics are questionable because one or more of the following happened during export: a row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export, only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table.
Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. If desired, import parameters can be supplied so that only non-questionable statistics will be imported, and all questionable statistics will be recalculated.
The following parameter to imp
forces all statistics to be recalculated on import: statistics=recalculate
Getting around IMP-00013: only a DBA can import a file exported by another DBA
- give your user import/export full_database rights grant imp_full_database,exp_full_database to ppowner36;
- User import FROMUSER TOUSER imp owner/owner file=owner.dmp FROMUSER=OWNERA TOUSER=OWNERB; Where OWNERA is the user who originally owned the data. OWNERB is the new user who you want to import the data to.