Use multiple dump files for large Oracle exports

by Bob Watkins | May 29, 2007 7:00:00 AM

Tags: Storage, Databases, Bob Watkins, Oracle Corp., database, Oracle Tips Newsletter

Takeaway: Even as far back as Oracle 8i, the Export utility could handle large exports by splitting them into multiple output files. Bob Watkins reviews how this is done prior to Oracle 10g and what changes are necessary for 10g and later.

Oracle's Export utility (exp) is a useful part of an overall backup strategy. It allows you to restore as little as a single table, which is something that's difficult to do with regular datafile-based backups. But as databases and individual tables have grown in size, it's become difficult to create an export file large enough to contain the data.

Many DBAs still don't realize that Export can use multiple output files to store the exported data. If one disk volume doesn't have enough space, or the database exceeds the operating system's size limit for a single file, it's easy to split the export into multiple files.

Ever since Oracle 8i, the FILE parameter has been able to take multiple pathnames separated by commas. The FILESIZE parameter lets you indicate how much data to put into a file before switching to the next one. If Export runs out of names in the FILE list, it will begin prompting for additional ones.

For example, let's say that a full database export will require 6 GB of space, and the size limit for your operating system is 2 GB. You want to put the data on the /exp filesystem in the prod directory. The parameter file would include the following:

FILE=/exp/prod/exp01.dmp,/exp/prod/exp02.dmp,/exp/prod/exp03.dmp

FILESIZE=2G

In Oracle 10g, the new Data Pump export (expdp) works in a similar way but with some changes. Pathnames are not hard-coded as in earlier versions; instead, Directory objects are used to point to operating system directories. The FILE parameter is replaced by DUMPFILE, and for convenience, you can specify a wildcard to autogenerate names instead of listing them all.

In Oracle 10g, if you had created a Directory object named EXPDIR to point to /exp/prod, the above parameters would look like this:

DUMPFILE=expdir:exp%U.dmp

FILESIZE=2G

Filenames of exp01.dmp, exp02.dmp, and exp03.dmp would be generated during the Export.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.