Oracle7 Server Utilities

Import Basics

The basic concept behind Import is very simple. Import extracts the objects from an Export file and puts them into a database. Figure 2 - 1 illustrates the process of importing from an export file:

Figure 2 - 1. Importing an Export File

Table Objects: Order of Import

Table objects are imported from the export file in the following order:

1. table definitions

2. table data

3. table indexes

4. integrity constraints and triggers First, new tables are created. Then data is imported. After all data has been imported into all tables, indexes are built. Then triggers are imported, and integrity constraints are enabled on the new tables. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data (once when it was originally inserted and again during the import).

For example, if the EMP table has a referential integrity constraint on the DEPT table and the EMP table is imported first, then all EMP rows that reference departments that have not yet been imported into DEPT would be rejected provided that the constraints are enabled.

When data is imported into existing tables, however, the order of import can still produce referential integrity failures. In the situation given above, if the EMP table already existed and referential integrity constraints were in force, many rows could be rejected.

A similar situation occurs when a referential integrity constraint on a table references itself. For example, if SCOTT's manager in the EMP table is DRAKE, and DRAKE's row has not yet been loaded, then SCOTT's row will fail--even though it would be valid at the end of the import.

Suggestion: For the reasons mentioned above, it is a good idea to disable referential constraints when importing into an existing table. You can then re-enable the constraints after the import is completed.

Storage Parameters

By default, a table is imported into its original tablespace using the original storage parameters.

If the tablespace no longer exists, or the user does not have sufficient quota in the tablespace, the system uses the default tablespace for that user. If the user does not have sufficient quota in the default tablespace, the user's tables are not imported. (See "Reorganizing Tablespaces" to see how you can use this to your advantage.)

The Parameter OPTIMAL

The storage parameter OPTIMAL for rollback segments is not preserved during export and import.

The COMPRESS Option

If you specified COMPRESS=Y at export time, then the storage parameters for large tables are adjusted to consolidate all data imported for a table into its initial extent. To preserve the original size of an initial extent, you must specify at export time that extents not be consolidated. See

for a description of the COMPRESS parameter.

Character Set Translation

Export writes export files using the character set specified for the user session, for example, 7-bit ASCII or IBM Code Page 500 (EBCDIC). If necessary, Import automatically translates the data to the character set of its host system. Import converts character data to the user-session character set if that character set is different from the one in the Export file. See also

for a description of how Export handles character set issues.

Access Privileges

To use Import, you need the CREATE SESSION privilege to log on to the Oracle7 Server. This privilege belongs to the CONNECT role established during database creation.

You can do an import even if you did not create the export file. However, if the export file is a full database export (created by someone using the EXP_FULL_DATABASE role), then it will only be possible to import that file if you have the IMP_FULL_DATABASE role as well.

Read-Only Tablespaces

You cannot import into a read-only tablespace, unless you first declare it to be a read-write tablespace. When the import completes, you can return the tablespace to its read-only status.

Rollback Segments

When you initialize a database, Oracle creates a single system rollback segment (named SYSTEM). Oracle uses this rollback segment only for transactions that manipulate objects in the SYSTEM tablespace. This restriction does not apply if you intend to import only into the SYSTEM tablespace. However, if you want to import into a different tablespace, you must create a new rollback segment. For details on creating rollback segments, see Chapter 10 "Managing Rollback Segments" of the Oracle7 Server Administrator's Guide.

Compatibility

Import can read export files created by Export Version 5.1.22 and later.

Trusted Oracle7 Server

There are additional steps and considerations when you are importing to a Trusted Oracle7 Server database. The Trusted Oracle7 Server Administrator's Guide contains more guidelines for using Import with Trusted Oracle7 Server.