Oracle Exports and 2 GB file limitation

[Aspi Engineer]

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_e...@striderite.com

[Andrew Babb]

Hi,

Can't be certain about this one, but I seem to remember that 8i is now
supporting the concept of exporting to a file name, with a suffix sequence
number, and specifying the size of each portion.

This would be similar to an export to a pipe, which is being piped through
SPLIT.

Rgds
Andrew

[Thomas Kyte]

A copy of this was sent to Andrew Babb <and...@mail.com>
(if that email address didn't require changing)

On Fri, 14 May 1999 09:22:16 +0800, you wrote:

>Hi,
>
>Can't be certain about this one, but I seem to remember that 8i is now
>supporting the concept of exporting to a file name, with a suffix sequence
>number, and specifying the size of each portion.
>

yup, in 8i:

FILESIZE
Export supports writing to multiple export files and Import can read from
multiple export files. If you specify a value (byte limit) for the FILESIZE
parameter, Export will write only the number of bytes you specify to each dump
file.

>This would be similar to an export to a pipe, which is being piped through
>SPLIT.
>
>Rgds
>Andrew
>
>Aspi Engineer 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_e...@striderite.com

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

Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Thomas Kyte]

A copy of this was sent to Aspi Engineer <aspi_e...@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_e...@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