Using Export - Oracle 8i

This section describes how to use the Export utility, including what you need to do before you begin exporting and how to invoke Export.

Before Using Export
To use Export, you must run the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL) after the database has been created.

Note: The actual names of the script files depend on your operating system. The script file names and the method for running them are described in your Oracle operating system-specific documentation.

CATEXP.SQL or CATALOG.SQL needs to be run only once on a database. You do not need to run it again before you perform the export. The script performs the following tasks to prepare the database for Export:

  • Creates the necessary export views
  • Assigns all necessary privileges to the EXP_FULL_DATABASE role
  • Assigns EXP_FULL_DATABASE to the DBA role

Before you run Export, ensure that there is sufficient disk or tape storage space to write the export file. If there is not enough space, Export terminates with a write-failure error.

You can use table sizes to estimate the maximum space needed. Table sizes can be found in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:

select sum(bytes) from user_segments where segment_type=’TABLE’;

The result of the query does not include disk space used for data stored in LOB (large object) or VARRAY columns or partitions.

Invoking Export
You can invoke Export in one of the following ways:

  • Enter the following command:
  • exp username/password PARFILE=filename

    PARFILE is a file containing the export parameters you typically use. If you use different parameters for different databases, you can have multiple parameter files. This is the recommended method.

  • Enter the following command, adding any needed parameters:
  • exp username/password

    Note: The number of parameters cannot exceed the maximum length of a command line on the system.

  • Enter only the command exp username/password to begin an interactive session and let Export prompt you for the information it needs. The interactive method provides less functionality than the parameter-driven method. It exists for backward compatibility.

You can use a combination of the first and second options. That is, you can list parameters both in the parameters file and on the command line. In fact, you can specify the same parameter in both places. The position of the PARFILE parameter and other parameters on the command line determines what parameters override others. For example, assume the parameters file params.dat contains the parameter INDEXES=Y and Export is invoked with the following line:

exp system/manager PARFILE=params.dat INDEXES=N

In this case, because INDEXES=N occurs after PARFILE=params.dat, INDEXES=N overrides the value of the INDEXES parameter in the PARFILE.

You can specify the username and password in the parameter file, although, for security reasons, this is not recommended. If you omit the username/password combination, Export prompts you for it.

Invoking Export As SYSDBA
SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA. However, there may be a few situations in which you need to do so, usually at the request of Oracle technical support.

To invoke Export as SYSDBA, use the following syntax:

Note: Because the string "AS SYSDBA" contains a blank, most operating systems require that entire string ’username/password AS SYSDBA’ be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. Please see your operating system-specific documentation for information about special and reserved characters on your system. If either the username or password is omitted, Export will prompt you for it.

Getting Online Help
Export provides online help. Enter exp help=y on the command line to invoke it.

Export: Release - Production on Wed Oct 6 15:23:43 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:


Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Getting Online HelpGetting Online Help

The following keywords only apply to transportable tablespaces TRANSPORT_ TABLESPACE export transportable tablespace metadata (N) TABLESPACES list of tablespaces to transport

Export terminated successfully without warnings.

Specifying Export Parameters on the Command Line or in the Parameter File

You can specify Export parameters in three ways: from a command-line entry, by allowing Export to prompt you for parameter values, or in the parameter file.

Command-Line Parameter Entry
You can specify all valid parameters and their values from the command line using the following syntax:

Export Parameter Prompts
If you prefer to let Export prompt you for the value of each parameter, you can use the following syntax:

exp username/password

Export will display each parameter with a request for you to enter a value.

The Parameter File
The parameter file allows you to specify Export parameters in a file where they can easily be modified or reused. Create the parameter file using any flat file text editor. The command-line option PARFILE=filename tells Export to read the parameters from the specified file rather than from the command line. For example:

exp PARFILE=filename
exp username/password PARFILE=filename
The syntax for parameter file specifications is one of the following:

The following example shows a partial parameter file listing:

Additional Information: The maximum size of the parameter file may be limited by the operating system. The name of the parameter file is subject to the file naming conventions of the operating system.

You can add comments to the parameter file by preceding them with the pound (#) sign. Export ignores all characters to the right of the pound (#) sign.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 8i Topics