Oracle
2Gb or not 2gb-file limits in Oracle (part II)
Exporting (export) and 2Gb
2Gb size of exported file
When most versions of export are written, the default file action APIs are used on the creation of export files. This means that on many platforms it is not possible to export a file system file of 2Gb or greater than 2Gb.
However, there are still some options available for resolving the 2Gb limit when export:
u exporting files larger than 2Gb to a raw device is basically fine, but this first requires that the size of the bare device must be able to accommodate the entire export file.
You export to a named pipe that allows compression or cutting (Unix platform applicable).
See the article "Export a quick reference to a file larger than 2Gb on a UNIX platform" [note:30528.1].
U Export to tape (most platforms apply)
See the article "Exporting to Tape on UNIX systems" [note:30428.1]. (This article also pages a detailed description of how to export to a UNIX pipeline and a remote shell)
Üoracle8i is allowed to be exported to multiple small files to replace a single large file.
Other 2GB export issues
The Oracle allowable Zone (extent) has a maximum size of 2Gb. Unfortunately, there is a problem with export in most Oracle distributions, and when you export a large file and specify Compress=y, it is possible to include a value greater than 2Gb in the next storage clause of the exported file. This will cause the import to fail, even if the ignore=y is specified at import time. Oracle has reported this problem in [bug:708790] and has warned in [note:62436.1].
When export encounters a 2Gb limit, it will report an error similar to the following:
. . 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
The second problem is mentioned in [bug:185855], which points out that the Create tablespace command generated by a whole-Library export will use bytes as the file size, and if the file size exceeds 2Gb, a ORA-2237 error will be generated when importing. This problem can be solved by creating a table space in units of M instead of bytes before importing. [bug:490837] also points to similar problems.
Export to Tape
When exporting, the Volsize parameter is limited to below 4Gb, and on some platforms it may only be 2Gb.
This problem has been fixed in the oracle8i. This issue is described in [bug:490190].
Sql*loader and 2Gb
When Sql*loader tries to open a file that is more than 2Gb, the following error will be reported:
Sql*loader-500:unable to open file (Bigfile.dat)
SVR4 Error:79:value too large for defined data type
The example in [note:30528.1] can be slightly modified to allow large input files to be used in Sql*loader.
Oracle 8.0.6 has already implemented large file support for discard file and log file in Sql*loader, but it is still different for the input data file on each platform. The details of the input file size limit are recorded in [bug:948460]. [bug:749600] records the maximum size of discard file files.
Oracle and other 2GB issues
This section lists other 2Gb issues.
Oracle 8.0.5 has since delivered 64-bit versions of Oracle on most platforms. From the 8.0.5 Readme file you can see the corresponding introduction-[note:62252.1]
L DBV (Database verifier) may not be able to scan more than 2Gb of data files and will report DBV-100 errors. This error was reported in [bug:710888].
L If you want to create a file larger than 2Gb in Oracle, the SQL command line "DataFile ..." The size XXXXXX clause must be specified in either M or K, otherwise a "Ora-02237:invalid file size" error will be reported. This error was reported in [bug:185855].
L The table space limit must not exceed 2Gb before the Oracle 7.3.4 release. Like what:
ALTER USER <username> QUOTA 2500M on <tablespacename>
This will be reported as "Ora-2187:invalid quota specification." Error.
This error was reported in [bug:425831]. The solution is if a user needs to exceed the 2Gb limit, give him unlimited tablespace privileges.
L If the output file of spool reaches 2Gb, an error will occur. For example: Sqlplus's command spool.
L Some of the core functions in Oracle Tools do not support large files. This error is reported in [bug:749600] and has been fixed in Oracle 8.0.6 and version 8.1.6. Note, however, that this error has not been modified in Oracle 8.1.5 and any other patches. In addition, even if there are fixes, there will still be large file limits because not all of the code uses these core functions.
Note: [bug:749600] Although the core function is clarified, some parts of the code still have problems. For example: the input file in Sql*loader is read without using the core.
L The Utl_file package uses the above core function, so there is still a 2Gb limit in Oracle versions that are not fixed. <Package:UTL_FILE> is a Pl/sql package that allows file access in Pl/sql.
Large files in a specific platform
The following are references to large file support in some specific platforms. Although we have worked hard to keep the information of these articles up-to-date, it is still recommended that you test carefully for each operation when accessing large files.
Platform
Reference
AIX (RS6000/SP)
[note:60888.1]
Hp
[note:62407.1]
Digital Unix
[note:62426.1]
Sequent PTX
[note:62415.1]
Sun Solaris
[note:62409.1]
Windows NT
FAT file system supports maximum 4Gb files
NTFS file system supports maximum 16Tb files in theory
1. Please refer to [note:67421.1] before using large files on NT Oracle8
2. Oracle8.1.6 's dbverify program is problematic (refer to [bug:1372172])
3. A problem occurs when you automatically expand to 4Gb in 8.1.6/8.1.7 to cause the database to crash. (Refer to [bug:1668488])
