【MOS】Parameter FILESIZE - Multiple Export Files (文档 ID 290810.1)

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

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

export client uses US7ASCII character set (possible charset conversion)

export server uses US7ASCII NCHAR character set (possible ncharset conversion)

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

IMP-00047: unexpected file sequence number; expected 1 but found 2000

IMP-00132: first file in the multi-file export is 32

IMP-00000: Import terminated unsuccessfully

解决： 是否使用十六进制打开过 dmp 文件，如果是，那么肯定是修改错误，因为这里的“ IMP-00046: using FILESIZE value from export file of 281474976710656 ”，表示 280G ，而文件只有 280M ，这显然是修改错误。 Parameter FILESIZE - Make Export Write To Multiple Export Files (文档 ID 290810.1)

In this Document

Purpose Scope Details   1. Introduction.   2. Usage of Export Parameter: FILESIZE   3. Usage of Import Parameter: FILESIZE   4. Possible Warnings and Errors upon Export.   5. Possible Warnings and Errors upon Import.   6. Known Defects. References

APPLIES TO: Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.4 [Release 8.1.7 to 11.2] Information in this document applies to any platform. ***Checked for relevance on 01-Apr-2015*** 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 BUG:2040507  - FILESIZE PARAMETER SPECIFIED WITH EXP ON OS/390 NOT RECOGNIZED ON UNIX IMP NOTE:155477.1  - Parameter DIRECT: Conventional Path Export Versus Direct Path Export NOTE:30428.1  - Exporting To Tape On UNIX Systems NOTE:30528.1  - Large File Issues (2GB+) when Using Export (EXP-2 EXP-15) Import (IMP-2 IMP-21) Or SQL*Loader NOTE:62427.1  - 2Gb or Not 2Gb - File limits in Oracle

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/，如需转载，请注明出处，否则将追究法律责任。