1
Export

This chapter describes how to use the Export utility to write data from an Oracle database into an operating system file in binary format. This file is stored outside the database, and it can be read into another Oracle database using the Import utility (described in Chapter 2).

This chapter discusses the following topics:

• What Is the Export Utility?
• Before Using Export
• Invoking Export
• Export Modes
• Getting Online Help
• Export Parameters
• Example Export Sessions
• Using the Interactive Method
• Warning, Error, and Completion Messages
• Exit Codes for Inspection and Display
• Conventional Path Export Versus Direct Path Export
• Invoking a Direct Path Export
• Network Considerations
• Character Set and Globalization Support Considerations
• Instance Affinity and Export
• Considerations When Exporting Database Objects
• Transportable Tablespaces
• Exporting from a Read-Only Database
• Using Export and Import to Partition a Database Migration
• Using Different Releases and Versions of Export

What Is the Export Utility?

The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an Export file, as illustrated in Figure 1-1.

Figure 1-1 Exporting a Database

Text description of the illustration sut81001.gif

An Export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.

Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

You can also display the contents of an export file without actually performing an import. To do this, use the Import SHOW parameter. See SHOW for more information.

To load data from ASCII fixed-format or delimited files, use the SQL*Loader utility.

Before Using Export

Before you begin using Export, be sure you take care of the following items (described in detail in the following sections):

• Run the catexp.sql or catalog.sql script
• Ensure there is sufficient disk or tape storage to write the export file
• Verify that you have the required access privileges

Running catexp.sql or catalog.sql

To use Export, you must run the script catexp.sql or catalog.sql (which runs catexp.sql) after the database has been created.

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 in the data dictionary
• Creates the EXP_FULL_DATABASE role
• Assigns all necessary privileges to the EXP_FULL_DATABASE role
• Assigns EXP_FULL_DATABASE to the DBA role
• Records the version of catexp.sql that has been installed

Ensuring Sufficient Disk Space

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. You can find table sizes 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 in partitioned tables.

Verifying Access Privileges

To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.

The following schema names are reserved and will not be processed by Export:

• ORDSYS
• MDSYS
• CTXSYS
• ORDPLUGINS
• LBACSYS

Invoking Export

You can invoke Export and specify parameters by using any of the following methods:

• Command-line entries
• Interactive Export prompts
• Parameter files

Before you use one of these methods to invoke Export, be sure to read the descriptions of the available parameters. See Export Parameters.

Command-Line Entries

You can specify all valid parameters and their values from the command line using the following syntax:

exp username/password PARAMETER=value

or

exp username/password PARAMETER=(value1,value2,...,valuen)

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

Interactive Export Prompts

If you prefer to let Export prompt you for the value of each parameter, you can use the following syntax to start Export in interactive mode:

exp username/password

Export will display commonly used parameters with a request for you to enter a value. This method exists for backward compatibility and is not recommended because it provides less functionality than the other methods. See Using the Interactive Method.

Parameter Files

You can specify all valid parameters and their values in a parameter file. Storing the parameters in a file allows them to be easily modified or reused, and is the recommended method for invoking Export. If you use different parameters for different databases, you can have multiple parameter files.

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 first example does not specify the username/password on the command line to illustrate that you can specify them in the parameter file, although, for security reasons, this is not recommended.

The syntax for parameter file specifications is one of the following:

PARAMETER=value
PARAMETER=(value)
PARAMETER=(value1, value2, ...)

The following example shows a partial parameter file listing:

FULL=y
FILE=dba.imp
GRANTS=y
INDEXES=y
CONSISTENT=y

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.

You can specify a parameter file at the same time that you are entering parameters 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 which parameters take precedence. For example, assume the parameter file params.dat contains the parameter INDEXES=y and Export is invoked with the following line:

exp username/password 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 parameter file.

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, except in the following situations:

• At the request of Oracle technical support
• When using transportable tablespaces (see Transportable Tablespaces)

To invoke Export as SYSDBA, use the following syntax, adding any desired parameters or parameter filenames:

exp \'username/password AS SYSDBA\'

Optionally, you could also specify an instance name:

exp \'username/password@instance AS SYSDBA\' 

If either the username or password is omitted, Export will prompt you for it.

This example shows the entire connect string enclosed in single quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most operating systems require that the entire connect string 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. In this example, backslashes are used as the escape character. If the backslashes were not present, the command-line parser that Export uses would not understand the quotation marks and would remove them before calling Export.

See your Oracle operating system-specific documentation for more information about special and reserved characters on your system.

If you prefer to use the Export interactive mode, see Using the Interactive Method for more information.

Export Modes

The Export utility provides four modes of export:

• Full
• User (Owner)
• Table
• Tablespace

All users can export in table mode and user mode. Users with the EXP_FULL_DATABASE role (privileged users) can export in all modes. Table 1-1 shows the objects that are exported and imported in each mode. Also see Processing Restrictions.

To specify one of these modes, use the appropriate parameter (FULL, OWNER, TABLES, or TABLESPACES) when you invoke Export. See Export Parameters for information on the syntax for each of these parameters.

You can use conventional path Export or direct path Export to export in any of the first three modes. The differences between conventional path Export and direct path Export are described in Conventional Path Export Versus Direct Path Export.

Table 1-1  Objects Exported and Imported in Each Mode

Table-Level and Partition-Level Export

You can export tables, partitions, and subpartitions in the following ways:

• Table-level Export: exports all data from the specified tables
• Partition-level Export: exports only data from the specified source partitions or subpartitions

In all modes, partitioned data is exported in a format such that partitions or subpartitions can be imported selectively.

Table-Level Export

In table-level Export, you can export an entire table (partitioned or nonpartitioned) along with its indexes and other table-dependent objects. If the table is partitioned, all of its partitions and subpartitions are also exported. This applies to both direct path Export and conventional path Export. You can perform a table-level export in any Export mode.

Partition-Level Export

In partition-level Export, you can export one or more specified partitions or subpartitions of a table. You can only perform a partition-level export in Table mode.

For information on how to specify table-level and partition-level Exports, see TABLES.

Processing Restrictions

The following restrictions apply when you process data with the Export and Import utilities:

• Java classes, resources, and procedures that are created using Enterprise Java Beans (EJBs) are not placed in the export file.
• Constraints that have been altered using the RELY keyword lose the RELY attribute when they are exported.
• When a type definition has evolved and then data referencing that evolved type is exported, the type definition on the import system must have evolved in the same manner.

Getting Online Help

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

Export Parameters

The following diagrams show the syntax for the parameters that you can specify in the parameter file or on the command line. Following the diagrams are descriptions of each parameter.

Export_start

Text description of the illustration expstart.gif

ExpModes

Text description of the illustration expmodes.gif

ExpTSOpts (tablespaces_spec)

Text description of the illustration exptsopt.gif

ExpOpts

Text description of the illustration expopts.gif

ExpOpts_continued

Text description of the illustration expopts_cont.gif

ExpFileOpts

Text description of the illustration expfilop.gif

BUFFER

Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.

Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:

buffer_size = rows_in_array * maximum_row_size

If you specify zero, the Export utility fetches only one row at a time.

Tables with columns of type LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.

Example: Calculating Buffer Size

This section shows an example of how to calculate buffer size.

The following table is created:

CREATE TABLE sample (name varchar(30), weight number);

The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.

Therefore, the maximum row size is 56 (30+2+22+2).

To perform array operations for 100 rows, a buffer size of 5600 should be specified.

COMPRESS

Default: y

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

CONSISTENT

Default: n

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.

If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.

Table 1-2 shows a sequence of events by two users: user1 exports partitions in a table and user2 updates data in that table.

Table 1-2  Sequence of Events During Updates by Two Users

If the export uses CONSISTENT=y, none of the updates by user2 are written to the export file.

If the export uses CONSISTENT=n, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making it inconsistent.

If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.

Keep in mind the following points about using CONSISTENT=y:

• CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.
• Export of certain metadata may require the use of the SYS schema within recursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle Corporation recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.
• To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not.

  For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.

• A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

  If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

  To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.

  See Also: OBJECT_CONSISTENT

CONSTRAINTS

Default: y

Specifies whether or not the Export utility exports table constraints.

DIRECT

Default: n

Specifies whether you use direct path or conventional path Export.

Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer (evaluating buffer). This method can be much faster than a conventional path Export.

For information about direct path Exports, including security and performance considerations, see Invoking a Direct Path Export.

FEEDBACK

Default: 0 (zero)

Specifies that Export should display a progress meter in the form of a period for n number of rows exported. For example, if you specify FEEDBACK=10, Export displays a period each time 10 rows are exported. The FEEDBACK value applies to all tables being exported; it cannot be set on a per-table basis.

FILE

Default: expdat.dmp

Specifies the names of the export files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export files (see the parameter FILESIZE), you can specify multiple filenames to be used. For example:

exp scott/tiger FILE = dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048

When Export reaches the value you have specified for the maximum FILESIZE, Export stops writing to the current file, opens another export file with the next name specified by the FILE parameter, and continues until complete or the maximum value of FILESIZE is again reached. If you do not specify sufficient export filenames to complete the export, Export will prompt you to provide additional filenames.

FILESIZE

Default: Data is written to one file until the maximum size, as specified in Table 1-3, is reached.

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.

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 (see FILE for more information) or, if it has used all the names specified in the FILE parameter, it will prompt you to provide a new export filename. 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.

The FILESIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits.

Table 1-3 shows that the maximum size for dump files depends on the operating system you are using and on the release of the Oracle database server that you are using.

Table 1-3  Maximum Size for Dump Files

The FILESIZE value can also 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).

FLASHBACK_SCN

Default: none

Specifies the system change number (SCN) that Export will use to enable flashback. The export operation is performed with data consistent as of this specified SCN.

FLASHBACK_TIME

Default: none

Specifies a time. Export finds the SCN that most closely matches the specified time. This SCN is used to enable flashback. The export operation is performed with data consistent as of this SCN.

FULL

Default: n

Indicates that the Export is a full database mode Export (that is, it exports the entire database). Specify FULL=y to export in full database mode. You need to have the EXP_FULL_DATABASE role to export in this mode.

GRANTS

Default: y

Specifies whether or not the Export utility exports object grants. The object grants that are exported depend on whether you use full database mode or user mode. In full database mode, all grants on a table are exported. In user mode, only those granted by the owner of the table are exported. System privilege grants are always exported.

HELP

Default: none

Displays a description of the Export parameters. Enter exp help=y on the command line to invoke it.

INDEXES

Default: y

Specifies whether or not the Export utility exports indexes.

LOG

Default: none

Specifies a filename to receive informational and error messages. For example:

exp SYSTEM/password LOG=export.log

If you specify this parameter, messages are logged in the log file and displayed to the terminal display.

OBJECT_CONSISTENT

Default: n

Specifies whether or not the Export utility uses the SET TRANSACTION READ ONLY statement to ensure that the data exported is consistent to a single point in time and does not change during the export. If OBJECT_CONSISTENT is set to y, each object is exported in its own read-only transaction, even if it is partitioned. In contrast, if you use the CONSISTENT parameter, then there is only one read-only transaction.

OWNER

Default: none

Indicates that the Export is a user-mode Export and lists the users whose objects will be exported. If the user initiating the export is the DBA, multiple users may be listed.

PARFILE

Default: none

Specifies a filename for a file that contains a list of Export parameters. For more information on using a parameter file, see Invoking Export.

QUERY

Default: none

This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter.

For example, if user scott wants to export only those employees whose job title is SALESMAN and whose salary is less than 1600, he could do the following (this example is UNIX-based):

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

When executing this query, Export builds a SQL SELECT statement similar to the following:

SELECT * FROM emp WHERE job='SALESMAN' and sal <1600; 
 

The values specified for the QUERY parameter are applied to all tables (or table partitions) listed in the TABLE parameter. For example, the following statement will unload rows in both emp and bonus that match the query:

exp scott/tiger TABLES=emp,bonus QUERY=\"WHERE job=\'SALESMAN\' and sal\<1600\"
 

Again, the SQL statements that Export executes are similar to the following:

SELECT * FROM emp WHERE job='SALESMAN' and sal <1600;

SELECT * FROM bonus WHERE job='SALESMAN' and sal <1600;

If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be exported for the offending table.

Restrictions

• The parameter QUERY cannot be specified for full, user, or tablespace mode exports.
• The parameter QUERY must be applicable to all specified tables.
• The parameter QUERY cannot be specified in a direct path export (DIRECT=y)
• The parameter QUERY cannot be specified for tables with inner nested tables.
• You cannot determine from the contents of the export file whether the data is the result of a QUERY export.

RECORDLENGTH

Default: operating system-dependent

Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.

If you do not define this parameter, it defaults to your platform-dependent value for buffer size. For more information about the buffer size default value, see your Oracle operating system-specific documentation.

You can set RECORDLENGTH to any value equal to or greater than your system's buffer size. (The highest value is 64 KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the disk. It does not affect the operating system file block size.

See your Oracle operating system-specific documentation to determine the proper value or to create a file with a different record size.

RESUMABLE

Default: n

The RESUMABLE parameter is used to enable and disable resumable space allocation. Because this parameter is disabled by default, you must set RESUMABLE=y in order to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.

RESUMABLE_NAME

Default: 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'

The value for this parameter identifies the statement that is resumable. This value is a user-defined text string that is inserted in either the USER_RESUMABLE or DBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended.

This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.

RESUMABLE_TIMEOUT

Default: 7200 seconds (2 hours)

The value of the parameter specifies the time period during which an error must be fixed. If the error is not fixed within the timeout period, execution of the statement is aborted.

This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.

ROWS

Default: y

Specifies whether or not the rows of table data are exported.

STATISTICS

Default: ESTIMATE

Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.

In some cases, Export will place the precalculated statistics in the export file as well as the ANALYZE statements to regenerate the statistics.

However, the precalculated optimizer statistics will not be used at export time if a table has columns with system-generated names.

The precalculated optimizer statistics are flagged as questionable at export time if:

• There are row errors while exporting
• The client character set or NCHAR character set does not match the server character set or NCHAR character set
• A QUERY clause is specified
• Only certain partitions or subpartitions are exported

  Note: Specifying ROWS=n does not preclude saving the precalculated statistics in the Export file. This allows you to tune plan generation for queries in a nonproduction database using statistics from a production database.

  See Also: Oracle9i Database Concepts

TABLES

Default: none

Specifies that the Export is a table-mode Export and lists the table names and partition and subpartition names to export. You can specify the following when you specify the name of the table:

• schemaname specifies the name of the user's schema from which to export the table or partition. The schema names ORDSYS, MDSYS, CTXSYS, and ORDPLUGINS are reserved by Export.
• tablename specifies the name of the table or tables to be exported. Table-level export lets you export entire partitioned or nonpartitioned tables. If a table in the list is partitioned and you do not specify a partition name, all its partitions and subpartitions are exported.

  The table name can contain any number of '%' pattern matching characters, which can each match zero or more characters in the table name against the table objects in the database. All the tables in the relevant schema that match the specified pattern are selected for export, as if the respective table names were explicitly specified in the parameter.

• partition_name indicates that the export is a partition-level Export. Partition-level Export lets you export one or more specified partitions or subpartitions within a table.

The syntax you use to specify the preceding is in the form:

schemaname.tablename:partition_name
schemaname.tablename:subpartition_name

If you use tablename:partition_name, the specified table must be partitioned, and partition_name must be the name of one of its partitions or subpartitions. If the specified table is not partitioned, the partition_name is ignored and the entire table is exported.

See Example Export Session Using Partition-Level Export for several examples of partition-level exports.

TABLES=\(emp,dept\) 

Table Name Restrictions

The following restrictions apply to table names:

• By default, table names in a database are stored as uppercase. If you have a table name in mixed-case or lowercase, and you want to preserve case-sensitivity for the table name, you must enclose the name in quotation marks. The name must exactly match the table name stored in the database.

  Some operating systems require that quotation marks on the command line be preceded by an escape character. The following are examples of how case-sensitivity can be preserved in the different Export modes.

  • In command-line mode:

    TABLES='\"Emp\"'
    
    

  • In interactive mode:

    Table(T) to be exported: "Emp"
    
    

  • In parameter file mode:

    TABLES='"Emp"'
    
    

• Table names specified on the command line cannot include a pound (#) sign, unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound (#) sign, the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks.

  For example, if the parameter file contains the following line, Export interprets everything on the line after emp# as a comment and does not export the tables dept and mydata:

  TABLES=(emp#, dept, mydata)
  
  

  However, given the following line, the Export utility exports all three tables because emp# is enclosed in quotation marks:

  TABLES=("emp#", dept, mydata)
  

  Note: Some operating systems require single quotation marks rather than double quotation marks, or the reverse; see your Oracle operating system-specific documentation. Different operating systems also have other restrictions on table naming. For example, the UNIX C shell attaches a special meaning to a dollar sign ($) or pound sign (#) (or certain other special characters). You must use escape characters to get such characters in the name past the shell and into Export.

For a TABLES parameter that specifies multiple schema.tablename:(sub)partition_name arguments, Export attempts to purge duplicates before processing the list of objects.

TABLESPACES

Default: none

The TABLESPACES parameter specifies that all tables in the tablespace be exported to the Export dump file. This includes all tables contained in the list of tablespaces and all tables that have a partition located in the list of tablespaces. Indexes are exported with their tables, regardless of where the index is stored.

You must have the EXP_FULL_DATABASE role to use TABLESPACES to export all tables in the tablespace.

When TABLESPACES is used in conjunction with TRANSPORT_TABLESPACE=y, you can specify a limited list of tablespaces to be exported from the database to the export file.

TRANSPORT_TABLESPACE

Default: n

When specified as y, this parameter enables the export of transportable tablespace metadata.

TRIGGERS

Default: y

Specifies whether or not the Export utility exports triggers.

TTS_FULL_CHECK

Default: FALSE

When TTS_FULL_CHECK is set to TRUE, Export verifies that a recovery set (set of tablespaces to be recovered) has no dependencies (specifically, IN pointers) on objects outside the recovery set, and vice versa.

USERID (username/password)

Default: none

Specifies the username/password (and optional connect string) of the user performing the export. If you omit the password, Export will prompt you for it.

USERID can also be:

username/password AS SYSDBA

or

username/password@instance AS SYSDBA

If you connect as user SYS, you must also specify AS SYSDBA in the connect string. Your operating system may require you to treat AS SYSDBA as a special string, in which case the entire string would be enclosed in quotation marks. See Invoking Export As SYSDBA for more information.

VOLSIZE

Specifies the maximum number of bytes in an export file on each volume of tape.

The VOLSIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits. See your Oracle operating system-specific documentation for more information.

The VOLSIZE value can be specified as a number followed by KB (number of kilobytes). For example, VOLSIZE=2KB is the same as VOLSIZE=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 get the final file size (VOLSIZE=2048B is the same as VOLSIZE=2048).

Parameter Interactions

Certain parameters can conflict with each other. For example, because specifying TABLES can conflict with an OWNER specification, the following command causes Export to terminate with an error:

exp SYSTEM/password OWNER=jones TABLES=scott.emp

Similarly, OWNER and TABLES conflict with FULL=y.

Example Export Sessions

This section provides examples of the following types of Export sessions:

• Example Export Session in Full Database Mode
• Example Export Session in User Mode
• Example Export Sessions in Table Mode
• Example Export Session Using Partition-Level Export

In each example, you are shown how to use both the command-line method and the parameter file method.

Example Export Session in Full Database Mode

Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data.

Parameter File Method

> exp SYSTEM/password PARFILE=params.dat

The params.dat file contains the following information:

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y

Command-Line Method

> exp SYSTEM/password FULL=y FILE=dba.dmp GRANTS=y ROWS=y

Export Messages

Export: Release 9.2.0.1.0 - Production on Wed Feb 27 16:52:15 2002

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8DEC character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table            AQ$_INTERNET_AGENTS          0 rows exported
. . exporting table       AQ$_INTERNET_AGENT_PRIVS          0 rows exported
. . exporting table                    DEF$_AQCALL          0 rows exported
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
. . exporting table               DEF$_DEFAULTDEST          0 rows exported
. . exporting table               DEF$_DESTINATION          0 rows exported
. . exporting table                     DEF$_ERROR          0 rows exported
. . exporting table                       DEF$_LOB          0 rows exported
. . exporting table                    DEF$_ORIGIN          0 rows exported
. . exporting table                DEF$_PROPAGATOR          0 rows exported
. . exporting table       DEF$_PUSHED_TRANSACTIONS          0 rows exported
. . exporting table                  DEF$_TEMP$LOB          0 rows exported
. . exporting table       LOGSTDBY$APPLY_MILESTONE          0 rows exported
. . exporting table        LOGSTDBY$APPLY_PROGRESS
. . exporting partition                             P0          0 rows exported
. . exporting table                LOGSTDBY$EVENTS          0 rows exported
. . exporting table            LOGSTDBY$PARAMETERS          0 rows exported
. . exporting table                 LOGSTDBY$PLSQL          0 rows exported
. . exporting table                   LOGSTDBY$SCN          0 rows exported
. . exporting table                  LOGSTDBY$SKIP          0 rows exported
. . exporting table      LOGSTDBY$SKIP_TRANSACTION          0 rows exported
. . exporting table        REPCAT$_AUDIT_ATTRIBUTE          2 rows exported
. . exporting table           REPCAT$_AUDIT_COLUMN          0 rows exported
. . exporting table           REPCAT$_COLUMN_GROUP          0 rows exported
. . exporting table               REPCAT$_CONFLICT          0 rows exported
. . exporting table                    REPCAT$_DDL          0 rows exported
. . exporting table             REPCAT$_EXCEPTIONS          0 rows exported
. . exporting table              REPCAT$_EXTENSION          0 rows exported
. . exporting table                REPCAT$_FLAVORS          0 rows exported
. . exporting table         REPCAT$_FLAVOR_OBJECTS          0 rows exported
. . exporting table              REPCAT$_GENERATED          0 rows exported
. . exporting table         REPCAT$_GROUPED_COLUMN          0 rows exported
. . exporting table      REPCAT$_INSTANTIATION_DDL          0 rows exported
. . exporting table            REPCAT$_KEY_COLUMNS          0 rows exported
. . exporting table           REPCAT$_OBJECT_PARMS          0 rows exported
. . exporting table           REPCAT$_OBJECT_TYPES         28 rows exported
. . exporting table       REPCAT$_PARAMETER_COLUMN          0 rows exported
. . exporting table               REPCAT$_PRIORITY          0 rows exported
. . exporting table         REPCAT$_PRIORITY_GROUP          0 rows exported
. . exporting table      REPCAT$_REFRESH_TEMPLATES          0 rows exported
. . exporting table                 REPCAT$_REPCAT          0 rows exported
. . exporting table              REPCAT$_REPCATLOG          0 rows exported
. . exporting table              REPCAT$_REPCOLUMN          0 rows exported
. . exporting table         REPCAT$_REPGROUP_PRIVS          0 rows exported
. . exporting table              REPCAT$_REPOBJECT          0 rows exported
. . exporting table                REPCAT$_REPPROP          0 rows exported
. . exporting table              REPCAT$_REPSCHEMA          0 rows exported
. . exporting table             REPCAT$_RESOLUTION          0 rows exported
. . exporting table      REPCAT$_RESOLUTION_METHOD         19 rows exported
. . exporting table  REPCAT$_RESOLUTION_STATISTICS          0 rows exported
. . exporting table    REPCAT$_RESOL_STATS_CONTROL          0 rows exported
. . exporting table          REPCAT$_RUNTIME_PARMS          0 rows exported
. . exporting table              REPCAT$_SITES_NEW          0 rows exported
. . exporting table           REPCAT$_SITE_OBJECTS          0 rows exported
. . exporting table              REPCAT$_SNAPGROUP          0 rows exported
. . exporting table       REPCAT$_TEMPLATE_OBJECTS          0 rows exported
. . exporting table         REPCAT$_TEMPLATE_PARMS          0 rows exported
. . exporting table     REPCAT$_TEMPLATE_REFGROUPS          0 rows exported
. . exporting table         REPCAT$_TEMPLATE_SITES          0 rows exported
. . exporting table        REPCAT$_TEMPLATE_STATUS          3 rows exported
. . exporting table       REPCAT$_TEMPLATE_TARGETS          0 rows exported
. . exporting table         REPCAT$_TEMPLATE_TYPES          2 rows exported
. . exporting table    REPCAT$_USER_AUTHORIZATIONS          0 rows exported
. . exporting table       REPCAT$_USER_PARM_VALUES          0 rows exported
. . exporting table        SQLPLUS_PRODUCT_PROFILE          0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table                            OL$          0 rows exported
. . exporting table                       OL$HINTS          0 rows exported
. . exporting table                       OL$NODES          0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. . exporting table                           DEPT          8 rows exported
. . exporting table                        MANAGER          4 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.

Example Export Session in User Mode

User mode exports can be used to back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another. In this example, user