Oracle Utilities Tips
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
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
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  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
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
Connected to: Oracle9i
Enterprise Edition Release 220.127.116.11.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 18.104.22.168.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
About to export specified tables via Direct Path ...
. . exporting table TABLE_WITH_ONE_MILLION_ROWS 1000000 rows
Export terminated successfully without warnings.
object_consistent [N] Specifies whether export uses SET
TRANSACTION READ ONLY to ensure that the data being exported
owner Only the owners 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 written 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
resumable 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
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
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:
To learn more about these techniques, see
the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code depot
of utilities scripts.