exp和expdp的filesize参数的使用—导出多个文件
Содержание
- exp和expdp的filesize参数的使用—导出多个文件
- [转]FILESIZE参数 – 输出写入到多个导出文件
- APPLIES TO:
- PURPOSE
- SCOPE
- DETAILS
- REFERENCES
- APPLIES TO:
- SOLUTION
coco3600 2019-04-16 14:02:52 
2383 
收藏 2
exp和expdp的filesize参数的使用—导出多个文件
如：exp userid=lhr/lhr file=/tmp/test1,test2,test3,test4,test5 filesize=2G log=test.log
$ expdp user/pwd directory=dump_file dumpfile=expdp_20190416_%U.dmp logfile=expdp_20100820.log filesize=500M parallel=4
[转]FILESIZE参数 – 输出写入到多个导出文件
甲骨文公司服务器 — 企业版 — 版本：8.1.7至10.2
甲骨文公司服务器 — 个人版 — 版本：8.1.7至10.2
甲骨文公司服务器 — 标准版 — 版本：8.1.7至10.2
注：62427.1 “2GB或不2GB — 文件限制在Oracle”
6) 相比于处理多个小文件，从文件查看点处理一个单一的大型输出转储文件更加困难。 因此，250GB的数据，如果需要导出，建议创建多个较小的文件，例如：指定FILESIZE=25G，创建10个规模较小的转储文件。
8) 当导出到一个命名管道，不推荐使用FILESIZE参数。 如果使用FILESIZE参数时使用命名管道，请确保您预先为每个出口转存文件创建一个命名管道。有关详情，请参阅：
FILESIZE值可以指定为KB（千字节数）级。例如，FILESIZE=2KB和FILESIZE= 2048是相同的。同样，MB指定兆字节（1024 * 1024）和GB指定千兆字节（1024** 3）。
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
E.g:
.
continuing export into file exp_f2.dmp
.
— exp_f1.dmp with size of 750 Mb
— exp_f2.dmp with size of 750 Mb
— exp_f3.dmp with size of 500 Mb
例如：在上面的例子，如果出口总额为3.5 GB，然后出口将创建4个750 MB的文件，并会提示为剩余的500 MB数据创建新文件。键入下一个出口转储文件的文件名后，出口将继续下去。
Example:
.
Export file: EXPDAT.DMP exp_f5.dmp
.
E.g:
%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 .
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".
% 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' .
输入数据时，必须使用导入参数FILESIZE告诉导入最大的出口指定转储文件的大小。FILESIZE值可以指定为KB（千字节数）级。例如，FILESIZE=2KB和FILESIZE= 2048是相同的。同样，MB指定兆字节（1024 * 1024）和GB指定千兆字节（1024** 3）。
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
.
Import file: EXPDAT.DMP exp_f3.dmp
.
1) EXP-75： FILESIZE四舍五入式下降
% 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 .
.
%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
如果FILESIZE参数值小于RECORDLENGTH参数值，将产生一个错误。例如如果出口开始于RECORDLENGTH= 8192和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
%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
.
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
% 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.
% 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
% 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:
.
%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
% 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
% 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
% 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
.
Import file: EXPDAT.DMP exp_f4.dmp
.
% 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
%impsystem/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
Parameter FILESIZE — Make Export Write To Multiple Export Files (文档 ID 290810.1)
In this Document
APPLIES TO:
Oracle Database — Enterprise Edition — Version 8.1.7.0 to 11.2.0.4 [Release 8.1.7 to 11.2]
Oracle Database Cloud Schema Service — Version N/A and later
Oracle Database Exadata Cloud Machine — Version N/A and later
Oracle Cloud Infrastructure — Database Service — Version N/A and later
Oracle Database Backup Service — Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 01-Apr-2015***
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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:
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
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).
Note 30428.1 — Exporting to Tape on Unix System
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:
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/password PARFILE=exp.par
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:
— 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).
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/password 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:
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/password 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.:
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/password 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.:
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:
% imp system/password 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.:
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/password 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
% imp system/password 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.:
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/password 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.:
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/password 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.:
5.5. IMP-48: mismatched file header
If you specify a wrong filename by mistake, the following errors can occur:
% imp system/password 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.:
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 FILESIZE4GB 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
How to Export to Multiple Dumpfiles Using Datapump Export and Import Again (文档 ID 778943.1)
In this Document
APPLIES TO:
Oracle Database — Enterprise Edition — Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 25-Jun-2013***
You are exporting a very large database, schemas or any other database objects and for whatever reason you would like to split the .dmp file created into smaller and more manageable export .dmp files. You are using datapump export.
SOLUTION
To export to multiple .dmp files using datapump you can use the DUMPFILE datapump export parameter and specify more than one file for export to write to or you can use a template with a substitution variable in the file name to create multiple files with names automatically assigned.
Syntax and Description
The directory_object is optional if one has already been established by the DIRECTORY parameter. If you supply a value here, it must be a directory object that already exists and that you have access to. A database directory object that is specified as part of the DUMPFILE parameter overrides a value specified by the DIRECTORY parameter or by the default directory object.
You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth.
If the FILESIZE parameter is specified, each dump file will have a maximum of that size in bytes and be nonextensible. If more space is required for the dump file set and a template with a substitution variable (%U) was supplied, a new dump file is automatically created of the size specified by FILESIZE, if there is room on the device.
As each file specification or file template containing a substitution variable is defined, it is instantiated into one fully qualified filename and Export attempts to create it. The file specifications are processed in the order in which they are specified. If the job needs extra files because the maximum file size is reached, or to keep parallel workers active, then additional files are created if file templates with substitution variables were specified.
Although it is possible to specify multiple files using the DUMPFILE parameter, the export job may only require a subset of those files to hold the exported data. The dump file set displayed at the end of the export job shows exactly which files were used. It is this list of files that is required in order to perform an import operation using this dump file set.
In the following example, we will export the SCOTT schema and will use a template with a substitution variable (%U) and the FILESIZE parameter to create dump file of 500K size. We will then use these sames files and name format to import the schema back into the database.
EXPORT TO MULTIPLE FILES
expdp system/manager SCHEMAS=scott DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir1:exp%U.dmp FILESIZE=500K
Export: Release 10.2.0.3.0 — 64bit Production on Wednesday, 28 January, 2009 20:54:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 — 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** SCHEMAS=scott DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir1:exp%U.dmp FILESIZE=500K
Estimate in progress using BLOCKS method.
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 768 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."IND_TABLE" 452.5 KB 2230 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/testcases/rvazquez/exp01.dmp
/testcases/rvazquez/exp02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:56:33
After export completes, we can see that 2 .dmp files were created automatically.
IMPORT MULTIPLE FILES
We can use the same syntax to run the import. This time we do not need the FILESIZE parameter. FILESIZE is only needed for export datapump. We have dropped the SCOTT user prior to execution of import datapump.
impdp system/manager SCHEMAS=scott DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir1:exp%U.dmp
Import: Release 10.2.0.3.0 — 64bit Production on Wednesday, 28 January, 2009 21:17:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 — 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** SCHEMAS=scott DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir1:exp%U.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."IND_TABLE" 452.5 KB 2230 rows
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:17:54
Please refer to:
Oracle® Database Utilities 10g Release 2 (10.2) Part Number B14215-01
Oracle® Database Utilities 11g Release 2 (11.2) Part Number E22490-05
Источник: