Hello DBAs : I guess export dumps have a limitation of 2GB per file or something right. If I have a table more than 2GB, then how would I do it.. What are all the choices I might have.
it's OS dependent, it is not an Oracle feature ... if your system cannot create files larger than 2 Gb, then there is no workaround for Oracle to do so ...
Thanks pipo. So what are the workarounds.. How can I get this done... Thanks ST
if you are under Unix, you can use compress or gzip to compress on the fly what is exported, so maybe you won't reach 2 Gigs.
also note that a 2Gb table will surely have a really smaller dump since all the allocated space is not used !!
Thanks Pipo.. My table size is 4.5GB and how do I handle this.. Please xplain in detail or an example by steps as I know only how to export and import files less than 2GB.. I am not a pro in unix as well and this is AIX machine.. ST
Prior to 8i, you need to compress the file while exp dump file is being written. This can be done using named pipe in UNIX.
# This shell script exports jpmc db and compress it
# using named pipe
# Author Tamil
# Date : Aug 17, 2001
# In Unix prompt, run jpmc_export.sh 2>jpmc_export.log
NLS_LANG=American_America.US7ASCII; export NLS_LANG
# First create a named pipe
mknod exp_pipe p
# export prod db
DX=`date '+%Y%m%d'`; export DX
exp system/password full=Y recordlength=65535 compress=N direct=Y file=exp_pip
# EOF prod_export.sh
Another method is using filesize in exp parameters. This is very easy to accomplish >2gb problem
exp system/password full=Y file=(a1.dmp, a2.dmp, a3.dmp) filesize=2000000000 compress=N direct=Y consistent=Y buffer=100000000 log=exp_prod.log
have you ever compressed on the fly AND used multiple export files (in one export)? i've been wondering about this as my compressed exports get up to 1.8G...
Large Export Files
If your using 8i, you can break the export file into smaller seperate files. If your using an earlier version and on Unix, there are tricks you can pull to break your export file into seperate chunks. I have to do this as I have a large database on 7.3.4.
Another point to remember on exporting large databases is that I believe that Oracle still has a limit of 2Gbytes per extent size. So if you export with compress = Y, your dump file may have a table create statement with an initial extent value greater than 2Gbytes. Just something else to think about in this topic.
I have export 4G data in one file .
Click Here to Expand Forum to Full Width