Home Archives Search Feed

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 the imp command. The example below imports all the data belonging to the owner 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

  1. give your user import/export full_database rights grant imp_full_database,exp_full_database to ppowner36;
  2. 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.

November 13, 2008

Previous post
Discover Linux System Resources You’ve just inherited a Linux machine and you need to know how many CPUs it has, what speed they go at, how many memory slots there are and what’s
Next post
Working with filenames containing spaces in Bash Sometimes you’ll need to manipulate a list of filenames which contain spaces using a ‘for’ loop. Let’s say you have the following two files: