export

The export utility (exp ) enables DBAs to extract information from the database. Exports play a vital role in a backup and recovery strategy, and are a convenient way to duplicate production data in test environments.

They are used for many reasons, including:

· Logical database backups.

· Copying data from one database to another (used in combination with import).

· Reorganization of segments (utilizing compress=y).

The export utility extracts objects (along with their dependent objects) and their data from the database and writes the data to an export file. This file is in binary format and can reside either on disk or tape.

Exporting to disk is much faster, but may not be an option for large databases where free disk space is limited. The export files are only used by the import utility to load the data into a different database or back into the same one. The version of the import utility cannot be older than the export version used.

Export Modes

There are four data export modes:

· Full – exports the contents of the entire database. This can be time consuming and requires substantial disk space, depending on the size of the database.

· User – exports all objects within a particular schema. If user SCOTT performs a user mode export, all of the objects belonging to SCOTT will be exported.

· Table – exports the DDL and data (optional) of any listed tables, table partitions, or subpartitions. If the table specified is partitioned, then all of its partitions will be exported. Table names can be specified using wildcard characters. In the example below, all of user DAVE’s tables will be exported, along with any table owned by SCOTT that contains a ‘D’, and any table owned by AARON that contains an ‘S’ in the table name.

TABLES=(scott.%D%,dave.%,aaron.%S%)

· Tablespace – exports all the tables residing in the specified tablespace(s). All indexes on the corresponding tables will also be exported, regardless of their tablespace (which should always be a different one than the table). If a table has one partition stored in the specified tablespace, the entire table (all partitions) will be exported. This option requires the exp_full_database role.

Each export mode addresses a different requirement and should be used appropriately. For instance, full exports should not be performed when only a few schemas have the data that needs to be exported.

Export Options

In addition to export modes, the export utility enables the user to specify runtime parameters interactively, on the command line or defined in a parameter file (PARFILE). These options include:

· buffer – Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.

· compress – When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used. Although this option is only implemented on import, it can only be specified on export.

· consistent – [N] Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

· constraints – [Y] Specifies whether table constraints should be exported with table data.

· direct – [N] Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance.

· feedback – [0] Determines how often feedback is displayed. A value of feedback=n displays a dot for every n rows processed. The display shows all tables exported not individual ones. From the output below, each of the 20 dots represent 50,000 rows, totaling 1 million rows for the table.

About to export specified tables via Direct Path ...

. . exporting table TABLE_WITH_ONE_MILLION_ROWS

....................

1000000 rows exported

· file – The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.

· filesize – The maximum file size, specified in bytes.

· flashback_scn – The system change number (SCN) that export uses to enable flashback.

· flashback_time – Export will discover the SCN that is closest to the specified time. This SCN is used to enable flashback.

· full – The entire database is exported.

· grants – [Y] Specifies object grants to export.

· help – Shows command line options for export.

· indexes – [Y] Determines whether index definitions are exported. The index data is never exported.

· log – The filename used by export to write messages. The same messages that appear on the screen are written to this file:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...

. . exporting table TABLE_WITH_ONE_MILLION_ROWS 1000000 rows exported

Export terminated successfully without warnings.

· object_consistent – [N] Specifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent.

· owner – Only the owner’s objects will be exported.

· parfile – The name of the file that contains the export parameter options. This file can be used instead of specifying all the options on the command line for each export.

· query – Allows a subset of rows from a table to be exported, based on a SQL where clause (discussed later in this chapter).

· recordlength – Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is writen to disk. If not specified, this parameter defaults to the value specific to that platform. The highest value is 64KB.

· resumable – [N] Enables and disables resumable space allocation. When “Y”, the parameters resumable_name and resumable_timeout are utilized.

· resumable_name – User defined string that helps identify a presumable statement that has been suspended. This parameter is ignored unless resumable = Y.

· resumable_timeout – [7200 seconds] The time period in which an export error must be fixed. This parameter is ignored unless resumable = Y.

· rows – [Y] Indicates whether or not the table rows should be exported.

· statistics – [ESTIMATE] Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.

· tables – Indicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.

· tablespaces – Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.

· transport_tablespace – [N] Enables the export of metadata needed for transportable tablespaces.

· triggers – [Y] Indicates whether triggers defined on export tables will also be exported.

· tts_full_check – [FALSE] When TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.

· userid – Specifies the userid/password of the user performing the export.

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

The functionality of the export utility has been significantly enhanced in recent versions of Oracle. To check which options are available in any release use:

exp help=y

Parameter Files

Parameter files are a convenient way to consolidate all the options from a file that will be used when executing the utility. The benefit of the parameter file is that it allows the options to be specified once and reused by all utility jobs. Three of the utilities discussed in this chapter (export, import, SQL*Loader) support parameter files.

Below is an example of an export parameter file: export_options.par.

compress=n

direct=n

buffer=1000

tables=table_with_one_million_rows

userid=scott/tiger

Using this parameter file, the export command line is executed by the following:

exp parfile=export_options.par

Specifying options in a file makes it much easier to implement the options with any utility that accepts a parameter file. In addition, these options are not revealed on the command line, and therefore not exposed to commands ( UNIX ps command ) that would reveal the username and password had they been specified on the command line.

Exporting Data Subsets

This export utility allows the DBA to limit the number of rows exported, based on a SQL where clause. This is very useful when only a portion of the table needs to be exported. For example, to export only those rows from the table whose order_number > 873737:

QUERY=\"WHERE order_number \> 873737\"

The specification of the query text must compensate for special characters that are specific to the operating system. The query text above places the entire string in double quotes and places an escape character (\) in front of special characters. The query specification above would result in the following SQL being executed:

select * from orders where order_number > 873737;

Subsetting is just one method by which export performance can be improved. The next section discusses other ways to optimize export performance.

Maximizing Export Performance

Many DBAs are faced with the challenge of speeding up utility functions such as export. Typically, an organization has only a small window for maintenance, and utility jobs must complete within that timeframe. Fortunately, there are a few things a DBA can do to expedite exports. These include:

· Use Direct Path – Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

· Use Subsets – By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

· Use a Larger Buffer – For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance. The following formula can be used to determine a proper buffer size:

buffer size = rows in array * max row size

· Separate Tables – Separate those tables that require consistent=y from those that don’t, in order to expedite the export. This way, the performance penalty will only be incurred for those tables that actually require it.

For the table with one million rows, the following benchmark tests were performed using the different export options.

Export Type	Elapsed Time (seconds)	Time Reduction
conventional	55	-
buffer=2800000	50	9%
direct = y	55	0 %
direct = y recordlength = 50000	41	25%

Table 4.1- Benchmark tests performed using the different export options.

The table above reveals a small improvement in performance was obtained by increasing the buffer size on a conventional export. Using direct=y offered no performance boost over conventional, until it was accompanied by recordlength, which reduced the elapsed time by 25 percent.

Once data has been successfully copied to an export file, it can then be used by the import utility, as described in the next section.