Exporting databases larger than 2 GB on UNIX

by Scott Stephens | Apr 19, 2005 2:15:00 PM

Tags: Databases, Scott Stephens, database, system call, Oracle Corp., split command, Unix, Oracle Tips Newsletter

Takeaway: Many UNIX variants used system calls that operated on files with 32-bit unsigned integers to indicate file offset; these integers are limited to a range of 0 to 2 gigabytes. See how you can use Oracle's split trick to export a file up to around 10 GB and still stay under the 2 GB limit.

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Many UNIX variants used system calls that operated on files with 32-bit unsigned integers to indicate file offset. Unfortunately, 32-bit unsigned integers are limited to a range of 0 to 2 gigabytes.

When UNIX systems began accepting files that were larger than the 2 GB maximum, it required applications and utilities to use alternate system calls, such as fseek64, to operate on these large files. This decision was made to avoid breaking older programs, which still used 32-bit integers, and the original system calls without needing to be recompiled.

The Oracle import and export utilities were written with the original system calls, internally. This meant that you could not export or import a file larger than 2 GB on a UNIX system unless you used some tricks to split the file into pieces less than 2 GB.

Before Oracle 8i, you could use the split command and named pipes to write the output of exp to multiple files. The split command would take standard input and write its output to multiple files, switching to a new file whenever a limit was reached. For example:

mknodpipe.dmp p

split -b 2047m < pipe.dmp &

exp system/manager file=pipe.dmp full=y

rmpipe.dmp

This process would create files called xaa, xab, xac, and so on. To import these files, you could run the commands:

mknodpipe.dmp p

cat xaaxabxac > pipe.dmp &

imp system/manager file=pipe.dmp full=y

rmpipe.dmp

Since Oracle 8i, Oracle added a command-line parameter filesize, which does something similar to the split command. It automatically creates the export as a set of files instead of a single file. It's important to make sure that the value for filesize is less than 2 GB. It's also important to specify the same parameter on import:

exp system/manager file=big.dmpfilesize=2000M full=y

imp system/manager file=big.dmpfilesize=2000M full=y

Since an export file is fairly compressible, you can still use the split trick to export a file up to around 10 GB and stay under the 2 GB limit.

mknodpipe.dmp p

compress < pipe.dmp > expdat.dmp.Z &

exp system/manager file=pipe.dmp full=y

rmpipe.dmp

Here's the code to import a file:

mknodpipe.dmp p

uncompress expdat.dmp.Z > pipe.dmp &

imp system/manager file=pipe.dmp full=y

rm pipe.dmp

Print/View all Posts Comments on this article

here how you can have unlimited sizejohn_miller_portland@...  | 04/22/05

Why would it have a 10gig limit?Beilstwh  | 02/10/06

Why would it have a 10gig limit?knannest@...  | 10/03/06