Import

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 it will recompute them 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 type’s 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

Maximizing Import Performance

Regardless of which options were used when the data was exported, it has no influence on how the data is imported. For example, it is irrelevant to the import process whether it was a direct path export or not, since it is a plain export file, be it generated from direct or conventional means.

Unfortunately, there is no direct option available for imports (only for export and SQL*loader). The import process has more tuning limitations than other utilities. The DBA should consider the following when trying to optimize import performance:

· Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase. Larger tables may not be suitable for this option due to the required rollback/undo space.

· Set indexes=n – Index creation can be postponed until after import completes, by specifying indexes=n. If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table. Setting indexes=n eliminates this maintenance overhead.

· Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.

When tuning import, emphasize reducing the amount of work that import needs to do. This can be accomplished by committing less frequently, not importing indexes, not generating statistics, or by using the buffer parameter to reduce disk access.

For the table with one million rows, the following benchmark tests were performed using the different import options. The table was truncated after each import.

Import Option	Elapsed Time (Seconds)	Time Reduction
commit=y	120	-
commit=y buffer=64000	100	17%
commit=n buffer=30720	72	40%
commit=N buffer = 64000	67	44%

Table 4.2 - Shows that increasing the size of the buffer has a positive performance impact.

The table above shows that increasing the size of the buffer has a positive performance impact. However, the most dramatic increase in performance was obtained when setting commit=n. The increase in the size of the buffer resulted in a marginal improvement when commit=n.

Before devising a strategy for using export / import to copy data from one database to another, the SQL*Plus copy command should be considered.