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.
NOTE:
Original Export is desupported for general use as of Oracle Database 11g. The only supported use of original Export in Oracle Database 11g is backward migration of XMLType data to Oracle Database 10g release 2 (10.2) or earlier. Therefore, Oracle recommends that you use the new Data Pump Export and Import utilities, except in the following situations which require original Export and Import:
Please refer to:
http://docs.oracle.com/database/121/SUTIL/original_export.htm#SUTIL3634
DETAILS
1. Introduction.
- 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 - 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.
- 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)).
- 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). - 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.
- 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 - 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).
FILESIZE=0 (default) export writes to one single file
FILESIZE=1024
or:
FILESIZE=1K
or:
FILESIZE=1KB causes export to write to 1 kilobyte files
FILESIZE=1M or:
FILESIZE=1MB causes export to write to 1 Megabyte files
FILESIZE=1G or:
FILESIZE=1GB causes export to write to 1 Gigabyte files
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:
File: exp.par
-------------
FILESIZE=750MB
FILE=exp_f1.dmp,exp_f2.dmp,
exp_f3.dmp,exp_f4.dmp
FULL=Y
DIRECT=Y
LOG=exp_full.log
% exp system/manager PARFILE=exp.par
Remark 1. When export allocates a new export dumpfile, this will be logged in the export logfile, e.g.:
...
continuing export into file exp_f2.dmp
...
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:
...
Export file: EXPDAT.DMP > exp_f5.dmp
...
Remark 4. Note that the command line syntax to specify filenames for the FILE parameter is:
-- possible syntax for Command Line mode (2 examples):
% exp ... file=exp_f1.dmp exp_f2.dmp exp_f3.dmp exp_f4.dmp ...
% exp ... file=exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp ...
-- or on Windows (3 additional examples):
D:\> exp ... file=(exp_f1.dmp exp_f2.dmp exp_f3.dmp exp_f4.dmp) ...
D:\> exp ... file=(exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp) ...
D:\> exp ... file="exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp" ...
-- or on Unix (3 additional examples):
% exp ... file=\(exp_f1.dmp exp_f2.dmp exp_f3.dmp exp_f4.dmp\) ...
% exp ... file=\(exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp\) ...
% exp ... file='exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp' ...
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).
FILESIZE=0 (default) export reads from one single file
FILESIZE=1024 or:
FILESIZE=1K or:
FILESIZE=1KB causes export to read from 1 kilobyte files
FILESIZE=1M or:
FILESIZE=1MB causes export to read from 1 Megabyte files
FILESIZE=1G or:
FILESIZE=1GB causes export to read from 1 Gigabyte files
Example: to run a table level import from the export dumpfiles created above.
File: imp.par
-------------
FILESIZE=750MB
FILE=exp_f1.dmp,exp_f2.dmp,
exp_f3.dmp,exp_f4.dmp
FROMUSER=scott
TOUSER=scott
TABLES=emp
LOG=imp_emp.log
% imp system/manager PARFILE=imp.par
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:
...
Import file: EXPDAT.DMP > exp_f3.dmp
...
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:
% exp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp, \
exp_f4.dmp LOG=exp_f.log FILESIZE=1000m \
DIRECT=y RECORDLENGTH=65535 FULL=y
Export: Release 10.2.0.3.0 - Production on Thu Dec 20 17:19:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
EXP-00075: rounding FILESIZE down, new value is 1048560000
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
...
Solution: ignore the warning or specify a multiple of the recordlength for the FILESIZE parameter, e.g.:
% exp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp, \
exp_f4.dmp LOG=exp_f.log FILESIZE=1048560000 \
DIRECT=y RECORDLENGTH=65535 FULL=y
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
% exp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp, \
exp_f4.dmp LOG=exp_f.log FILESIZE=64k \
DIRECT=y RECORDLENGTH=65535 FULL=y
Export: Release 10.2.0.3.0 - Production on Thu Dec 20 17:39:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
EXP-00075: rounding FILESIZE down, new value is 65535
EXP-00073: dump file size too small
EXP-00000: Export terminated unsuccessfully
Solution: Specify a larger value for the FILESIZE parameter, e.g.:
% exp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp, \
exp_f4.dmp LOG=exp_f.log FILESIZE=640m \
DIRECT=y RECORDLENGTH=65535 FULL=y
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:
...
EXP-00030: Unexpected End-Of-File encountered while reading input
.. exporting table EMP_LONG error clossing export file
EXP-00002: Error in writing to export file
EXP-00002: Error in writing to export file
EXP-00000: Export terminated unsuccessfully
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:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:00:48 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00046: using FILESIZE value from export file of 786432000
...
Import terminated successfully with warnings.
Solution: Ignore the warning, and next time when importing from this export dumpfile set, specify the correct value for the FILESIZE parameter, e.g.:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
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.:
% imp system/manager FILE=exp_f3.dmp, exp_f2.dmp, exp_f1.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:05:04 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00047: unexpected file sequence number; expected 1 but found 3
IMP-00132: first file in the multi-file export is exp_f1.dmp
IMP-00000: Import terminated unsuccessfully
or:
% imp system/manager FILE=exp_f1.dmp, exp_f3.dmp, exp_f2.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:13:39 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00047: unexpected file sequence number; expected 2 but found 3
IMP-00132: first file in the multi-file export is exp_f1.dmp
IMP-00008: unrecognized statement in the export file:
...
Solution: Specify the dumpfiles in the correct order, e.g.:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
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:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=75m \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:18:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00040: FILESIZE does not match the value used for export: 786432000
IMP-00000: Import terminated unsuccessfully
Solution: Restart the import and specify a correct value for the FILESIZE parameter, e.g.:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
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:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dm LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:21:02 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00002: failed to open exp_f4.dm for read
Import file: EXPDAT.DMP >
Solution: specify the correct filename, or alternatively re-start the import with the correct names for the dumpfiles, e.g.:
...
Import file: EXPDAT.DMP > exp_f4.dmp
...
5.5. IMP-48: mismatched file header
If you specify a wrong filename by mistake, the following errors can occur:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f.log LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
Import: Release 10.2.0.3.0 - Production on Thu Dec 20 18:28:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
IMP-00048: mismatched file header
IMP-00008: unrecognized statement in the export file:
IMP-00000: Import terminated unsuccessfully
-- or import aborts with:
...
IMP-00048: mismatched file header
IMP-00009: abnormal end of export file
IMP-00000: Import terminated unsuccessfully
Solution: Restart the import and specify the correct set of dumpfile names, e.g.:
% imp system/manager FILE=exp_f1.dmp, exp_f2.dmp, exp_f3.dmp \
exp_f4.dmp LOG=imp_emp.log FILESIZE=750m \
FROMUSER=scott TOUSER=scott TABLES=emp
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
