How do I export a table when the dump file is too big for my operating system (There is a 2GB limit) ?

The Oracle (tm) Users' Co-Operative FAQ

How do I export a table when the resulting file is too big for my operating system ?

Up until version 8.1, there were really only a couple of options available to you in this scenario :

• exporting directly to tape.
• compressing on the fly as the exp took place.

Compressing on the fly would only work where the final size of the export file was less than 2 Gb compressed. (I'm assuming that as per the question, the database has a single table which takes up more than 2 Gb when exported uncompressed.) I use this process on my 7.3.4 instances to reduce the amount of disc space used for my exports.

From Oracle 8.1.5 the options are extended slightly by allowing multiple filenames to be specified for the FILE parameters and a new FILESIZE parameter has been added so that exp will stop writing to one file when it hits the limit specified by the user. If exp runs out of filenames and still has data to export, it will stop and prompt for the next filename whenever it needs one.

Export directly to tape

Probably the simplest, but not the quickest. Simply specify a tape device name for the FILE parameter and give an (optional) VOLSIZE to tell exp how big the tape is :

	exp user/password .... file=/dev/rmt/0m volsize=20G ....

The above command will export directly to the tape device /dev/rmt/0m (which is the first tape drive on an HP server) and will write a maximum of 20 Gb before requesting a new tape. You will be prompted to switch tapes as each one is written to.

Importing is equally as easy, simply specify the tape device as the FILE parameter and load up the first tape in the sequence. As each tape is used, load the next one.

Compress on the fly

This option usually requires a script, but the basics are quite simple :

• create a pipe file
• create a background task to cat everything from the pipe to gzip and redirect the output from gzip to the required filename
• exp and specify the pipe file as the FILE parameter

On my HP server this looks like the following :

	# Set up the user to be exported
	export USERNAME=test
	export PASSWORD=test
	# Create the pipe
	rm $USERNAME.pipe
	/usr/sbin/mknod $USERNAME.pipe p
	# Create the background task
	cat $USERNAME.pipe | gzip -9 > $USERNAME.dmp.gz &
	# Export the user to the pipe
	exp $USERNAME/$PASSWORD ... file=$USERNAME.pipe ...

Importing also requires a bit of work, but basically it is the reverse of what we have done above :

• create a pipe file
• gunzip the compressed dump file and redirect the output to the pipe file
• imp from the pipe file

Again, on my HP server this looks like the following :

	# Set up the user to be exported
	export USERNAME=test
	export PASSWORD=test
	# Create the pipe
	rm $USERNAME.pipe
	/usr/sbin/mknod $USERNAME.pipe p
	# Create the background task
	gunzip -c $USERNAME.dmp.gz > $USERNAME.pipe &
	# Import the user from the pipe
	imp $USERNAME/$PASSWORD ... file=$USERNAME.pipe ...

Use FILESIZE under 8.1.5 onwards

This new option make exporting a lot simpler than before. Simply give exp a list of filenames and specify a FILESIZE :

	exp user/password .... file=file_1,file_2,file_3,file_4 filesize=2G ....

The above command will export a maximum of 2 Gb to file_1, then switch to file_2 and export another 2G and so on. If the list of filenames is exhausted before the data, then you will be prompted to supply a new filename whenever one is required.

Importing is simply a matter of supplying the list of file names in the correct order.

VOLSIZE and FILESIZE parameters

You can specify values using the usual Oracle shortcuts. Exp recognises numbers with 'B', 'K', 'M' or 'G' and adjusts the actual byte value accordingly. In addition, the value calculated is the correct multiple of 1,024 and not the usual 'hard drive manufacturer' multiple of 1,000. For example, all of the following specify a size of 2Gb.

• FILESIZE=2G
• FILESIZE=2048M
• FILESIZE=2097152K
• FILESIZE=214748364B
• FILESIZE=2147483648

In otherwords, 'K' = 1024¹, 'M' = 1024², 'G' = 1024³. Lower case letters are also recognised.

Further reading:

Oracle 8 Utilities Manual

Oracle Performance Tuning - Curry & Corrigan. O'Reilly Press. Chapter 14 Tuning Database Backup & Recovery.