|Author's name: Norman Dunbar
Author's Email: NormanDunbar@cwcom.net
|Date written: 17/09/2001
Oracle version(s): 7.3.4 & 8.1.7
|How do I export a table when the resulting file is too big for my operating system ?|
Back to index of questions
Up until version 8.1, there were really only a couple of options available to you in this scenario :
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.
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.
This option usually requires a script, but the basics are quite simple :
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 :
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 ...
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.
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.
In otherwords, 'K' = 10241, 'M' = 10242, 'G' = 10243. Lower case letters are also recognised.
Oracle 8 Utilities Manual
Oracle Performance Tuning - Curry & Corrigan. O'Reilly Press. Chapter 14 Tuning Database Backup & Recovery.
Back to top
Back to index of questions