Re: Oracle Exports and 2 GB file limitation

A copy of this was sent to Aspi Engineer <aspi_engineer_at_striderite.com> (if that email address didn't require changing) On Thu, 13 May 1999 19:03:10 -0400, you wrote:

>Hi,
>
>We are running Oracle 8i on AIX 4.3 and wanted to find out if Oracle
>will allow you to export a table/file that is more than 2G in size. If
>yes, then what is the maximum file size that can be exported before I
>need to think about partitioning the database tables.
>
>Thanks
>Aspi Engineer
>aspi_engineer_at_striderite.com

In Oracle8i, release 8.1, there is a filesize parameter to export. It works like this for example. This exports to files of a max size of 100k:

$ exp filesize=100k owner=scott userid=scott/tiger 'file=(1.dmp,2.dmp,3.dmp)'

Export: Release 8.1.5.0.0 - Production on Fri May 14 08:11:43 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table RUPD$_EMP
. . exporting table EMP_SNAPSHOT 14 rows exported
. . exporting table MLOG$_EMP 0 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table EMP 14 rows exported
. . exporting table MYTABLE 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures

continuing export into file 2.dmp

. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
Export terminated successfully without warnings.

$ ls -l
total 185

-rw-r--r--   1 tkyte      102400 May 14 08:08 1.dmp
-rw-r--r--   1 tkyte       74752 May 14 08:08 2.dmp

Another method is to use named pipes. This works with very large exports. It exports to a named pipe, compresses the export data off of the pipe, and uses split to create OS files of a given maximum size. For example, I do this on my database and end up with:

-rw-r--r--   1 oracle8  524288000 May  8 21:51 exp.128_1999.dmp.aa
-rw-r--r--   1 oracle8  524288000 May  8 22:09 exp.128_1999.dmp.ab
-rw-r--r--   1 oracle8  524288000 May  8 22:28 exp.128_1999.dmp.ac
-rw-r--r--   1 oracle8  524288000 May  8 22:48 exp.128_1999.dmp.ad
-rw-r--r--   1 oracle8  357108009 May  8 23:32 exp.128_1999.dmp.ae

So the compressed output is 2.3gig spread across 5 files each of which is a maximum of 500meg in size.

This script also tests the integrity of the compressed, split files by running an imp show=y after we are done. this just reads the export data to make sure we can read it. It has the nice side effect of creating a export.test file with all of the DDL to create my database (nice to recover that procedure you accidently overwrote for example).

#!/bin/csh -vx

setenv UID sys/xxxxx
setenv FN exp.`date +%j_%Y`.dmp
setenv PIPE /tmp/exp_tmp.dmp

setenv MAXSIZE 500m
setenv EXPORT_WHAT full=y

echo $FN

cd /nfs/atc-netapp1/expbkup
ls -l

rm expbkup.log export.test exp.*.dmp* $PIPE mknod $PIPE p

date > expbkup.log
( gzip < $PIPE ) | split -b $MAXSIZE - $FN. & exp userid=$UID buffer=20000000 file=$PIPE $EXPORT_WHAT >>& expbkup.log date >> expbkup.log

date > export.test
cat `echo $FN.* | sort` | gunzip > $PIPE & imp userid=sys/o8isgr8 file=$PIPE show=y full=y >>& export.test date >> export.test

tail expbkup.log
tail export.test

ls -l
rm -f $PIPE

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 14 1999 - 07:13:30 CDT