Oracle export file size 2 GB Overview

My summary will certainly help you:

2 GB Export File Size

When writing this article, most export versions use the default file processing API to create export files.

. This means that files larger than 2 GB cannot be exported on many platforms.

Here are some ways to overcome them:
Generally, files larger than 2 GB can be exported to bare devices.
You can use named pipes on UNIX to compress/split files.
Can be exported to disk
Oracle8i allows exporting data to multiple files instead of a large file.




Other 2 GB export problems

The maximum extent size of Oracle is 2 GB. Unfortunately

Export has a problem, that is, when compress = y is specified, the next file may be exported

The storage clause may be larger than 2 GB. This causes import

An error occurs.

Compress = y can modify the storage clause initial to specify whether to compress the data segment of the fragment.

To facilitate the use of large extent during reconstruction.

For details about this issue, see [BUG: 708790] and [Note: 62436.1].




Typical 2 GB + export error:
.. Exporting table bigexport
EXP-00015: Error on Row 10660 of table
Bigexport,
Column mycol, datatype 96
EXP-00002: Error in writing to export file
EXP-00002: Error in writing to export file
EXP-00000: Export terminated unsuccessfully


In [BUG: 185855], we also mentioned a problem: Create tablespace generated when the full database is exported

The command uses bytes as the unit. If the data file generated during import is greater than 2 GB

Caused a ora-2237 error.
The solution is to first create a tablespace (replace bytes with m) and then import the file.

Supplement:

The following methods can be taken for an export large file:
1. Bare Device
For example, direct output to/dev/rlvtest.

2. Named Pipe (under UNIX)
Mknod/tmp/imp_pipe P
Compress </tmp/exp_pipe> export. dmp. Z &
Exp file =/tmp/exp_pipe userid = xxx/xxx tables =...

Mknod/tmp/imp_pipe P
Uncompress <export. dmp. z>/tmp/imp_pipe &
IMP file =/tmp/imp_pipe userid = xxx/xxx tables =...

3. Compression/file Splitting: (The following is only valid in KSh :)
Echo | exp file = & gt; (compress | split-B 1024 M-expdmp -)

Userid = xxx/xxx tables =...
Echo | imp file = <(cat expdmp-* | zcat) USERID = xxx/xxx

Tables =...

4. The tape can be directly backed up.
For example, exp file =/dev/rmt0 ....

5. In Oracle8i +, you can use filesize and file to generate inverted

Multiple files.
**************************************** **********************
5. Command Line-full export
　　
Exp userid = cams/CAMs @ cams full = y inctype = complete
File = (/tmp/2004020601.dmp,/tmp/2004020602.dmp,/tmp/2004020603.dmp)
Filesize = 2 GB log =/tmp/20040206.log
　　
This command completely exports the full database data. The maximum size of each file to be exported to multiple files is 2 GB, and the export process logs are logged.

.
　　
Note:
　　
(1) When full-Database Export is performed, the incremental export type is (complete) by default.

It is exactly the same.
　　
6. Command Line-cumulative Export
　　
Exp userid = cams/CAMs @ cams full = y inctype = cumulative
File = (/tmp/2004020601.dmp,/tmp/2004020602.dmp) filesize = 2 GB
Log =/tmp/20040206.log
　　
This command cumulatively exports full-database data to multiple files. Each file has a maximum of 2 GB, and the export process record is daily.

Zhi.
　　
Note:
　　
(1) If you select the incremental export type, you must select full = Y, that is, you can only perform

Full accumulation or incremental export.
　　
7. Command Line-incremental Export
　　
Exp userid = cams/CAMs @ cams full = y inctype = Incremental
File = (/tmp/2004020601.dmp,/tmp/2004020602.dmp) filesize = 2 GB
Log =/tmp/20040206.log
　　
This command cumulatively exports full-database data to multiple files. Each file has a maximum of 2 GB, and the export process record is daily.

Zhi.