Export - Break the 2GB (or whatever) barrier!

Most experienced DBAs know of the trick of using a named pipe to compress the export output to get around the OS file limit size -- up to a point. This strategy fails if the compressed size itself exceeds the OS file size limit.

On a recent project I ran into this same old problem again where even compression led to a file to big for its own good. Then inspiration struck! Instead compressing the output read from the read-end of the name pipe, why don't I chop the output up into multiple files, where each of them has a size smaller than the OS file limit. The first thing I thought of using was Unix's 'split'. However, the problem with 'split' is that it puts all the resulting files in the same directory (which implies in the same file system). In this particular instance the biggest file system size we have is 4 GB. The table I'm exporting is about 7.5 GB. So 'split' only does half the job. Since I'm a fairly good Perl programmer, I whipped up a fairly straightforward Perl script that does what 'split' does, plus allowing me to specify multiple target directories (thereby different file systems) to store the resulting files.

So here is how I exported the 7.5 GB table:

exp scott/tiger file=named_pipe ... &
cat named_pipe | splicer.pl -chuck_size 1g -out_prefix exp.dmp. -dirs /dir1,/dir2,/dir3,/dir4

splicer.pl creates seven 1GB file and one 0.5GB file, spread across the four directories I specified.

And yes, import works! To import the data, you basically reverse steps used in the export process - a named pipe is needed also.

You can also put 'compress' between 'cat named_pipe' and 'splicer.pl' to have compressed, spliced export files.

I think I've given enough info for you to implement your own 'splicer.pl'. However, if you want to purchase a copy of my splicer.pl, I can be reached at pku.aol.com

later

Pei

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 04 1999 - 12:50:11 CST