Go FileSize parameter – output written to multiple export files

FileSize parameter – output written to multiple export files

1. Applicable to:

Oracle Server-Enterprise Edition-version: 8.1.7 to 10.2

Oracle Server-Personal version-version: 8.1.7 to 10.2

Oracle Server-Standard Edition-version: 8.1.7 to 10.2

The information in this document applies to any platform.

2. Purpose:

This document provides information about using the FileSize parameter when exporting data from an Oracle database or importing data back into an Oracle database.

3. Scope and application:

This manual is intended for exporting data from an Oracle database using the Export utility, and for creating multiple export dump files instead of a single (usually very large) oracle8i,oracle9i for exporting the dump file and for users of the oracle10g database. The manual provides information about using FileSize parameters, typical error messages, and some related flaws with possible workarounds.

4. Introduction:

1) By default, the output writes data to an egress dump 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 limit in Oracle"

2) First introduce oracle8i, output support write to multiple export files, input can be read into multiple export files. If you assign a value to the FileSize parameter (a byte limit), the egress will write only the data of the byte size you specified for the dump file.

3) on Oracle9i and later servers, the FileSize parameter has a maximum value that can be stored in 64 bits (16EB (ai bytes) = 16384 pb (Pb) =16777216tb (TB level) = The maximum value of 17179869184GB (gigabytes).

4) Classic output client (EXP) does not have a real dump file with the best size. Even if we only import a small table from a very large dump, we have to read through the complete dump file.

5) Note that this differs from the ORACLE10G data Pump Client (EXPDP and IMPDP). If we import a small table from multiple dump files, we can only read the data pump dump file header, and the Data pump master Table We read may be one of the dump files stored in a later location. Based on the information in the main table, we determine that the small table is in the dump file (s), and then we read only those specific dump files (s).

6) processing a single large output dump file from a file view point is more difficult than working with multiple small files. Therefore, 250GB of data, if you need to export, it is recommended to create several smaller files, for example: Specify filesize=25g, create 10 smaller dump files.

7) When exporting to a tape device, the filesize parameter is not used and the Volsize parameter is used. For more information, please see:

Note: 30428.1 "Export to tape on UNIX systems" "

8) When exporting to a named pipe, it is not recommended to use the FileSize parameter. If you use Named pipes when using the FileSize parameter, make sure that you create a named pipe in advance for each export dump file. For more information, please see:

Note: 30528.1 "Use Export (EXP-2 EXP-15), import (IMP-2IMP-21), or SQL * Loader when the problem of large files (2GB +)"

5. Usage of export parameters: FILESIZE

If you do not specify a file size (note that the value of 0 is equivalent to not specifying a filesize) value, the output will be written to a file regardless of the number of files specified in the file parameter.

If the amount of space you need to export the file exceeds the available disk space, the egress will abort and the output can be duplicated and completed after sufficient disk space is provided.

The FileSize value can be specified as a KB (kilobyte) level. For example, FILESIZE=2KB and filesize= 2048 are the same. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024** 3).

B is shorthand for the byte, and the number does not have to be multiplied by the byte size to obtain the final file size (filesize=2048b is filesize= 2048).

Filesize=0 (default) output is written to a single file

filesize=1024 or:

filesize=1k or:

FILESIZE=1KB output written to 1000 bytes of file

Filesize=1m or:

FILESIZE=1MB output writes 1 gigabytes of files

FILESIZE=1G or:

Filesize= 1GB, Output writes to 1000 gigabytes of files

When the output writes more data than the maximum value specified by FileSize, the output will get the name of the next output file from the file parameter, and if it already uses the name specified by all the file parameters, the output will prompt you for a new output file name.

If the amount of data output does not fit the list of files provided, the output will prompt for more file names.

If the output program runs in the background, make sure that you provide enough file names for the output. The output program hangs while waiting for you to provide an extra file name, and you may not be able to find that the file name is not sufficient.

For example: Run a direct path to the full database output and create a $ MB output dump file:

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

Note 1: When the export is assigned a new export dump file, this will be recorded in the export log.

e.g:
...
Continuing export into file exp_f2.dmp
...

Note 2: The file parameter has been specified, but it is not required for export, the specified file will not be created.

For example: In the above example, if the total output is 2 GB, then the output will create 3 files:

-Exp_f1.dmp with size of (MB)
-Exp_f2.dmp with size of (MB)
-Exp_f3.dmp with size of

Note 3: If the export requires more files, export the data and it will prompt for a new file name.

For example, in the example above, if the total export amount is 3.5 GB, then the egress will create 4 MB of files and will prompt for the remaining MB of data to create a new file. After you type the file name of the next export dump, the export continues.

Example:
...
Export File:expdat. DMP > Exp_f5.dmp
...

Note 4 Note that the file parameter specifies the command-line syntax for the file name:

---possible syntax for command-line mode (2 cases):

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 ...

or in Windows systems (3 by-laws):

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 systems (3 by-laws):

% 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 ' ...

6. Import Parameters: FILESIZE

When you enter data, you must use the import parameter, FileSize, to tell the import the maximum size of the dump file specified by the export. The FileSize value can be specified as a KB (kilobyte) level. For example, FILESIZE=2KB and filesize= 2048 are the same. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024** 3).

B is shorthand for the byte, and the number does not have to be multiplied by the byte size to obtain the final file size (filesize=2048b is filesize= 2048).

Filesize=0 (default) export reads a single file

filesize=1024 or:

filesize=1k or:

filesize=1kb Export Read 1000 bytes of file

Filesize=1m or:

FILESIZE=1MB export reads 1 megabytes of files

FILESIZE=1G or:

Filesize= 1GB, export read 1000 megabytes of files

For example, run a table-level import program from the export dump file 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

Note 1: These file names are ignored if there are too many files listed in the file parameters (such as file Exp_f4.dmp not created during the export process).

NOTE 2: If all filenames are not listed, the import will proactively request the next file name.

For example, if import begins to specify exp_f1.dmp,exp_f2.dmp two files without mentioning the third or last file name Exp_f3.dmp, the import prompts for the remaining data to create a new file name. After entering the correct file name, the import process will continue.

For example:

...
Import File:expdat. DMP > Exp_f3.dmp
...

7. Possible warnings and errors for export:

1) exp-75:filesize rounding-down

The value specified by the FileSize parameter is a multiple of the value of the RecordLength parameter. If this is not true, then the FileSize parameter value will be lowered 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 ...
...

WORKAROUND: Ignore the warning or specify a multiple of the filesize parameter to RecordLength:

%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

Note 1: Ensure that the new specified value is also used when importing filesize.

Note 2: For more information on parameter recordlength, see:

Note 3:155477.1 "Direct parameter: comparison between normal path export and direct path export"

2) EXP-73: Dump file is too small

If the FileSize parameter value is less than the RecordLength parameter value, an error is generated. For example, if the exits start at 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: FileSize parameters, such as specifying a larger value:

%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

3) EXP-2: Error writing to export file

If there is not enough space for the export to write to the dump file, or if another process prevents the export from writing to the dump file, the exit is aborted because of some of 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

Workaround: Make sure there is enough free space to create the file, make sure that there are no errors on the disk, and ensure that other processes, such as antivirus scanning, do not block the export from writing to the file and re-run the egress.

8. Possible warnings and errors when importing:

1) IMP-46: Use export file filesize value

If no value specifies the FileSize parameter in the import program, a warning is generated. However, the import process will continue to:

% 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.

WORKAROUND: Ignore the warning and specify the correct value filesize parameter the next time the file set input is dumped from this export, such as:

% 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

2) IMP-47: Unexpected file serial number

If the file name is listed in the wrong order, the system reports an error and the import program aborts, for example:

% 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 dump file in the correct order, for example:

%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

3) Imp-40:filesize does not match the value used by the export program

If the wrong filesize value is specified during the import process (for example, specifying filesize=75m, not filesize=750m), an error is 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 the correct values for the FileSize parameter, such as:

% 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

4) IMP-2: Failed to open read file

If you mistakenly specify an incorrect file name, 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 file name, or restart the import dump file with the correct name:

...
Import File:expdat. DMP > Exp_f4.dmp
...

5) IMP-48: file header mismatch

If you mistakenly specify an incorrect file name, the following error may 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 name of a series of correct dump files, such as:

%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

Go FileSize parameter – output written to multiple export files