Oracle Utilities Tips
Oracle
Import
The import utility (imp) reads files generated by the export
utility and loads the objects and data into the database.
Tables are created, their data is loaded, and the indexes are
built. Following these objects, triggers are imported,
constraints are enabled, and bitmap indexes are created.
This sequence is appropriate for a number of reasons. First,
rows are inserted before triggers are enabled to prevent the
firing of the triggers for each new row. Constraints are
loaded last due to referential integrity relationships and
dependencies among tables. If each EMPLOYEE row required a
valid DEPT row and no rows were in the DEPT table, errors
would occur. If both of these tables already existed, the
constraints should be disabled during the import and enabled
after import for the same reason.
Import Options
The import modes are the same as the export modes (Full, User,
Table, Tablespace) previously described. Imports support the
following options:
buffer Specifies the size, in bytes, of the buffer used to
insert the data.
commit [N] Specifies whether import should commit after
each array insert. By default, import commits after each table
is loaded, however, this can be quite taxing on the rollback
segments or undo space for extremely large tables.
compile [Y] Tells import to compile procedural objects as
they are imported.
constraints [Y] Specifies whether table constraints should
also be imported with table data.
datafiles Used only with transport_tablespace. This
parameter lists datafiles to be transported to the database.
destroy [N] Determines if existing datafiles should be
reused. A value of Y will cause import to include the reuse
option in the datafile clause of the create tablespace
statement.
feedback [0] Determines how often feedback is displayed. A
value of feedback=10 displays a dot for every 10 rows
processed. This option applies to the total tables imported,
not individual ones. Another way to measure the number of rows
that have been processed is to execute the following query
while the import is active:
select rows_processed
from v$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
file The name of the export file to import. Multiple files
can be listed, separated by commas. When export reaches the
filesize it will begin writing to the next file in the list.
filesize The maximum file size, specified in bytes.
fromuser A comma delimited list of schemas from which to
import. If the export file contains many users or even the
entire database, the fromuser option enables only a subset of
those objects (and data) to be imported.
full The entire export file is imported.
grants - [Y] Specifies to import object grants.
help Shows command line options for import.
ignore [N] Specifies how object creation errors should be
handled. If a table already exists and ignore=y, then the rows
are imported to the existing tables, otherwise errors will be
reported and no rows are loaded into the table.
indexes [Y] Determines whether indexes are imported.
indexfile Specifies a filename that contains index
creation statements. This file can be used to build the
indexes after the import has completed.
log The filename used by import to write messages.
parfile The name of the file that contains the import
parameter options. This file can be used instead of specifying
all the options on the command line.
recordlength Specifies the length of the file record in
bytes. This parameter is only used when transferring export
files between operating systems that use different default
values.
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 error must be fixed. This parameter is ignored unless
resumable=Y.
rows [Y] Indicates whether or not the table rows should be
imported.
show [N] When show=y, the DDL within the export file is
displayed.
skip_unusable_indexes [N] Determines whether import skips
the building of indexes that are in an unusable state.
statistics [ALWAYS] Determines the level of optimizer
statistics that are generated on import. The options include
ALWAYS, NONE, SAFE and RECALCULATE. ALWAYS imports statistics
regardless of their validity. NONE does not import or
recalculate any optimizer statistics. SAFE will import the
statistics if they appear to be valid, otherwise they will be
recomputed after import. RECALCULATE always generates new
statistics after import.
streams_configuration [Y] Determines whether or not any
streams metadata present in the export file will be imported.
streams_instantiation [N] Specifies whether or not to
import streams instantiation metadata present in the export
file.
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 When transport_tablespace=y, this parameter
provides a list of tablespaces.
toid_novalidate Specifies whether or not type validation
should occur on import. Import compares the types unique ID (TOID)
with the ID stored in the export file. No table rows will be
imported if the TOIDs do not match. This parameter can be used
to specify types to exclude from TOID comparison.
to_user Specifies a list of user schemas that will be
targets for imports.
transport_tablespace [N] When Y, transportable tablespace
metadata will be imported from the export file.
tts_owners When transport_tablespace=Y, this parameter
lists the users who own the data in the transportable
tablespace set.
userid Specifies the userid/password of the user
performing the import.
volsize Specifies the maximum number of bytes in an export
file on each tape volume.
To check which options are available in any release of import
use:
imp help=y
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.
|