2GB or NOT 2GB - FILE LIMITS in Oracle (Part II)
Export (export) and 2GB
2GB exported file size
When writing most of the version of Export, you use the default file operation API on the creation export file. This means that there is no possibility to export 2GB or greater than 2GB file system files on many platforms.
But there are still some options that can be used to resolve 2GB restrictions at Export:
ü Exported more than 2GB files to naked devices basically no problem, of course, this first requires naked devices to be able to accommodate the entire export file.
ü Export to a named pipe that allows compression or cutting (applicable UNIX platform).
See "Exporting a quick reference to 2GB files on the UNIX platform" (NOTE: 30528.1].
ü Export to tape (applicable to most platforms)
See "Export to Tape" in UNIX System [NOTE: 30428.1]. (This article also describes how to export to UNIX pipes and remote shells in the page.
ü Oracle8i allows you to export to multiple small files to replace a single big file.
Other 2GB export issues
The size of the Oracle Allow Zone (Extent) is 2GB. But unfortunately, exports exist in most Oracle distributions, when you export a big file, and specify compress = y, then it is possible to include one in the NEXT storage clause of the export file. More than 2GB value. This will result in the failure of the Import, even if IGNORE = Y is specified at Import. Oracle has reported this issue in [BUG: 708790] and warns in [NOTE: 62436.1].
When the export encounters 2GB limit, it will report to the following 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
The second problem is mentioned in [BUG: 185855]. This problem indicates that the CREATE TABLESPACE command generated by a library export will use Bytes in the file size. If the file size exceeds 2GB, then it will be generated when imported. An ORA-2237 error. This problem can be solved by creating a method such as a table space instead of bytes before importing. [BUG: 490837] also pointed out similar problems.
Export to tape
The Volsize parameter is limited to 4GB, and there may be only 2GB on some platforms.
This problem has been fixed in Oracle8i. This issue is described in [BUG: 490190].
SQL * LoAder and 2GB
When SQL * Loader is trying to open an over 2GB file, 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
An example in [NOTE: 30528.1] can be modified to make the use of large input files in SQL * LoADER.
Oracle
8.0.6
Big file support has been implemented in SQL * LOADER, but the Data file is still different on each platform. [BUG: 948460] Record details of the file size limit. [BUG: 749600] The largest DISCARD FILE file size is recorded. Oracle and other 2GB issues
This chapter lists other color 2GB issues.
l Oracle
8.0.5
Oracle provided 64-bit versions on most platforms later. You can see the corresponding introduction from the README file of 8.0.5 - [NOTE: 62252.1]
l DBV (Database Verification Program) may not scan more than 2GB of data files and report DBV-100 errors. This error is reported in [BUG: 710888].
l If you want to create more than 2GB file in Oracle, the "DataFile ... size xxxxxx" clause part of the SQL command line must be specified in m or K, otherwise, "ORA-02237: Invalid File Size" will be reported. error. This error is reported in [BUG: 185855].
l in Oracle
7.3.4
The release of the previous table space cannot exceed 2GB. such as:
Alter User
2500M
ON
This will report "ORA-2187: Invalid Quota Specification."
This error is reported in [BUG: 425831]. The solution is that if a user needs more than 2GB limit, then give him Unlimited TableSpace privileges.
l If the SPOOL output file reaches 2GB, there will be an error. For example: SQLPLUS command spool.
l Some of the Core functions in the Oracle tool does not support big files. This error is reported in Oracle in [BUG: 749600].
8.0.6
And version 8.1.6 has been fixed. But pay attention to this error in any patch in Oracle 8.1.5 and other patches. In addition, even if there is a correction, there is still a big file limit because all 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, if you enter a file in SQL * Loader, it is not using CORE.
l UTL_FILE Pack The above-described Core function is used, so there is still 2GB restrictions in the Oracle version that is not corrected.
Big file in a specific platform
Below is a reference material for large file support in some specific platforms. Although we have made our efforts to keep these articles to be updated, it is still recommended to be careful carefully to be carefully tested when you have a large file.
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 file
NTFS file system supports maximum 16TB files
1. Please refer to the use of large files on NT's Oracle8 [NOT: 67421.1]
2. Oracle
8.1.6
DBVERIFY program has problems (reference [BUG: 1372172])
3. in
8.1.6
