Using Oracle Import and Export
This is a description on how to use Oracle’s
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
oracle user on the source database host:
ssh-keygenon 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
imp to read data from that pipe
$ imp system/password@orcl fromuser=ppowner touser=ppowner file=/tmp/inpipe.dmp &
Set up a named pipe on the source host
$ mknod /tmp/outpipe.dmp p
Allow the pipe to be tunnelled over ssh
ssh -C oracle@dest-host 'cat >/tmp/inpipe.dmp' < /tmp/outpipe.dmp &
Set oracle exp to write to the named pipe
$ exp system/password@orcl owner=ppowner file=/tmp/outpipe.dmp
You should expect to see output from
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
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
expcommand is replaced with the
impcommand. The example below imports all the data belonging to the
owneruser into the new database.
$ imp userid=owner/owner grants=y indexes=y statistics=recalculate file=owner.dmp
exp can cause stale statistics to be imported.
During functional and performance testing, I regularly use Oracle
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:
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.