IMP-00046: using FILESIZE value from export file of 281474976710656

---

---

Parameter FILESIZE - Make Export Write To Multiple Export Files (文件 ID 290810.1)

In this Document

APPLIES TO:

PURPOSE

This document provides information about the usage of the FILESIZE parameter when exporting data from an Oracle database or importing data back into an Oracle database.

SCOPE

The article is intended for users of the Oracle8i, Oracle9i, and Oracle10g database who wish to use the EXPORT utility to export data from an Oracle database and create multiple export dump files, rather than one single (and usually very large) export dump file. The article gives information about the usage of the FILESIZE parameter, typical error messages, and some related defects with possible workarounds.

DETAILS

1. Introduction.

1. By default, an export writes data to one export dumpfile until the maximum size is reached. The maximum value that can be stored in a file is dependent on your operating system. See also: 
   Note 62427.1 - 2Gb or Not 2Gb - File limits in Oracle
   
   
2. Starting with Oracle8i, 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. 
   
   
3. In Oracle9i and higher, the FILESIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits (16 EB (exabyte) = 16384 PB (petabyte) = 16777216 TB (terabyte) = 17179869184 Gb (gigabyte)). 
   
   
4. With the classic export client (exp) there is not a real optimal size for the dumpfiles. Even if we have to import one small table from a very large dumpfile, we have to read through the complete dumpfile.
   
   Note that this is different with Oracle10g's Data Pump clients (expdp and impdp). If we have to import one small table from multiple dumpfiles, we only read the headers of the Data Pump dumpfiles, and we read the Data Pump Master Table which is stored in one of the last dumpfiles. Based on the information in the master table we determine in which dumpfile(s) the small table is located, and then we read only those specific dumpfile(s). 
   
   
5. From file handling point-of-view one single large export dumpfile is more difficult to handle than multiple smaller files. Therefore if 250 Gb of data needs to be exported, it is recommended to create multiple smaller files, e.g. specify FILESIZE=25G to create 10 smaller dumpfiles. 
   
   
6. When exporting to a tape device, do not use the FILESIZE parameter, but use the VOLSIZE parameter instead. For details, see also: 
   
   Note 30428.1 - Exporting to Tape on Unix System
   
   
7. When exporting to a named pipe, it is not recommended to use the FILESIZE parameter. When the FILESIZE parameter is used in combination with a named pipe, ensure that you pre-create one named pipe for each export dumpfile. For details, see also: 
   Note 30528.1 - Large File Issues (2Gb+) when Using Export (EXP-2 EXP-15), Import (IMP-2 IMP-21), or SQL*Loader

2. Usage of Export Parameter: FILESIZE

If you do not specify a value for FILESIZE (note that a value of 0 is equivalent to not specifying FILESIZE), then Export will write to only one file, regardless of the number of files specified in the FILE parameter. 

If the space requirements of your export file exceed the available disk space, Export will abort, and you will have to repeat the Export after making sufficient disk space available.

The FILESIZE value can be specified as a number followed by KB (number of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). 

'B' remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048B is the same as FILESIZE=2048).

When the amount of data Export must write, exceeds the maximum value you specified for FILESIZE, it will get the name of the next export file from the FILE parameter or, if it has used all the names specified in the FILE parameter, it will prompt you to provide a new export filename.

If the amount of data exported does not fit in the list of files provided, export will prompt for more filenames. 

Ensure to provide enough filenames to the export if running the export in the background. The export may hang while waiting for you to provide it with additional filenames which you will not be able to do.

Example: run a direct path full database export and create export dumpfiles with a size of 750 Mb:

Remark 1. When export allocates a new export dumpfile, this will be logged in the export logfile, e.g.:

Remark 2. Filenames that have been specified with the FILE parameter, and that are not needed for this export, will not be created.

E.g.: in the example above, if the total export is 2 Gb, then export will create 3 files: 
- exp_f1.dmp with size of 750 Mb 
- exp_f2.dmp with size of 750 Mb 
- exp_f3.dmp with size of 500 Mb

Remark 3. If export needs more files to export the data, it will prompt for a new filename.

E.g.: in the example above, if the total export is 3.5 Gb, then export will create 4 files with size of 750 Mb, and will prompt for a new filename for the remaining 500 Mb of data. After typing a filename for the next export dumpfile, export will continue. Example:

Remark 4. Note that the command line syntax to specify filenames for the FILE parameter is:

3. Usage of Import Parameter: FILESIZE

Upon import, you must use the Import parameter FILESIZE to tell Import the maximum dump file size you specified on export. The FILESIZE value can be specified as a number followed by KB (number of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). 

'B' remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048B is the same as FILESIZE=2048).

Example: to run a table level import from the export dumpfiles created above.

Remark 1. If too many files are listed for the FILE parameter (e.g. file exp_f4.dmp was not created during export), those filenames will be ignored. 

Remark 2. If not all filenames are listed, import will ask for the next filename. 

E.g. if import was started with FILE=exp_f1.dmp,exp_f2.dmp and the third and final filename exp_f3.dmp was not mentioned, import will prompt for a new filename for the remaining data. After typing the correct filename, import will continue. Example:

4. Possible Warnings and Errors upon Export.

4.1. EXP-75: rounding FILESIZE down
The value specified for the FILESIZE parameter has to be a multiple of the value of RECORDLENGTH parameter. If this is not true, then the value of the FILESIZE parameter will be rounded down automatically:

Solution: ignore the warning or specify a multiple of the recordlength for the FILESIZE parameter, e.g.:

Remark 1. Ensure that the new value for FILESIZE is also used upon import.

Remark 2. For details about parameter RECORDLENGTH, see also: 

Note 155477.1 - Parameter DIRECT: Conventional Path Export Versus Direct Path Export

4.2. EXP-73: dump file size too small
If the value of the FILESIZE parameter is smaller than the value of the RECORDLENGTH parameter, an error will be produced. E.g. If the export is started with RECORDLENGTH=8192 and FILESIZE=4KB

Solution: Specify a larger value for the FILESIZE parameter, e.g.:

4.3. EXP-2: Error in writing to export file
If there is insufficient space for export to write to the dumpfile, or another process prevents export to write to the dumpfile, then export may also abort with the following errors:

Solution: ensure that there is enough free space to create the files, ensure that the disk does not have any errors, ensure that other processes (like anti-virus scanners) are not preventing export to write to the file, and re-run the export.

5. Possible Warnings and Errors upon Import.

5.1. IMP-46: using FILESIZE value from export file 
If no value is specified for the FILESIZE parameter during the import, a warning will be produced. However, import will continue:

Solution: Ignore the warning, and next time when importing from this export dumpfile set, specify the correct value for the FILESIZE parameter, e.g.:

5.2. IMP-47: unexpected file sequence number
If the filenames are listed in the wrong order, an error will be reported and import will abort, e.g.: 

or:

Solution: Specify the dumpfiles in the correct order, e.g.:

5.3. IMP-40: FILESIZE does not match the value used for export
If a wrong value for FILESIZE is specified during import (e.g. specify FILESIZE=75m instead of FILESIZE=750m), an error will be reported:

Solution: Restart the import and specify a correct value for the FILESIZE parameter, e.g.:

5.4. IMP-2: failed to open file for read
If you specify an incorrect filename by mistake, import will prompt for the correct filename:

Solution: specify the correct filename, or alternatively re-start the import with the correct names for the dumpfiles, e.g.:

5.5. IMP-48: mismatched file header
If you specify a wrong filename by mistake, the following errors can occur:

Solution: Restart the import and specify the correct set of dumpfile names, e.g.:

6. Known Defects.

Bug 2040507 - FILESIZE PARAMETER SPECIFIED WITH EXP ON OS/390 NOT RECOGNIZED ON UNIX IMP
- Symptoms:  IMP-46 and IMP-40 are possible attempting to import MVS export to another platform
- Releases:  9.0.1.5 and lower
- Fixed in:  9.2.0.1 and higher; for IBM z/OS (OS/390) a fix on top of 9.0.1.4.0 is available with Patch 3253419
- Workaround:  run export on a client machine with different platform, using SQL*Net to connect.

Bug 1076041 - EXPORTS THAT USE FILESIZE>4GB FAIL WITH EXP-2
- Symptoms:  EXP-2 exporting to multiple dump files with FILESIZE >= 4Gb
- Releases:  8.1.5.x and 8.1.6.x
- Fixed in:  8.1.7.0 and higher
- Workaround: do not specify 4Gb filesize, but use lower value.

Bug 1522646 - INCONSISTENT BEHAVIOR OF HANDLING FILESIZE PARAMETER (not a public bug)
- Symptoms:  No error if value for FILESIZE was too low (e.g. FILESIZE=1024)
- Releases:  8.1.7.4 and lower
- Fixed in:  9.0.1.1. and higher
- Workaround:  specify a valid value for the FILESIZE parameter

REFERENCES

About Me

............................................................................................................................... ● 本文來自於MOS ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2138759/，如需轉載，請註明出處，否則將追究法律責任。