Import

Import extracts data from an export file and puts it back into an Oracle database. Also see Export.

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

• Table definitions
• Table data
• Table indexes
• Integrity constraints and triggers

First, new tables are created, then data is imported. After all data is imported into all tables, indexes are built. Then triggers are imported, and integrity constraints are enabled. If you prefer to import by specifying a list of tables, then the order in which the tables are imported in is important to avoid rejection of rows in a table due to not meeting referential integrity.

You can access key words used for exports by typing

imp HELP=y

To use Export utility CATEXP.SQL or CATALOG.SQL (which in turn call CATEXP.SQL) must be run after the database has been created. CATEXP.SQL has to be run only once when the database is created. In case CATEXP.SQL was not called by CATALOG.SQL (due to CATALOG.SQL not finding the file CATEXP.SQL where it needs), you can run CATEXP.SQL as user SYS. Be careful not to run CATALOG.SQL once again as it might destroy your data dictionaries.

By default if an object does not exist, when importing the object will be created in the tablespace as in the export file. If the tablespace does not exists then the object will not be created. For example let us say we have exported a table emp_mast which orginally exists in tablespace DATA01 in the production database. We now desire to import this table to test database (assuming table emp_mast not being in test database), if the test database does not have the tablespace DATA01, then the import will not be successful.

Required Privileges

Privilages necessary for Imports are listed Below:

• To use import, you need the CREATE SESSION privilege. The CONNECT role will have this privilege established during database creation.
• If user XYZ has created a export, then any user with IMP_FULL_DATABASE role granted to him (usually a DBA role will be granted this privilege at database creation) can import the export file.
• If user XYZ happens to be a DBA (user granted DBA role), then users only with IMP_FULL_DATABASE (dba role) can import this file.
• If user XYZ has created a export file, then the same userid (XYZ) on a different database can import this file.
• A user with IMP_FULL_DATABASE privilege, can only do a full database import.
• To be able to import privileges that a user has granted to others, the user initiating the import must either own the object or have object privilege with the WITH GRANT OPTION.

Tips

Some Tips While Importing

• The storage parameter OPTIMAL for rollback segments is not preserved during export and import.
• You cannot import into a read-only tablespace.
• Tables with LONG data usually require large insert buffer size. If you are trying to import LONG data and import fails with the IMP-00020 message, then you can increase the insert buffer size gradually (by 10,000 bytes at a time) up to 66,000 or greater.
• When a local stored procedure, function, or package is imported, it retains its original timestamp. If the time stamp of the imported version differs from the timestamp of the version currently in the database, it is marked for recompilation. If the timestamp matches, it is not marked and will not be recompiled.
• If a table is named emp#, and if you parameter file contains the line
  TABLES=(emp#, dept, invoice)
  Then the line succeeding emp# i.e. dept, invoice is treated as a comment, To get around with this condition enclose the table name having # in quotation marks
  TABLES=("emp#", dept, invoice)

I usually prefer to use parameter file as directive to imports, where in all the directives are written in a flat file and import utility reads directives from the parameter file. Sample parameter file (imp.par)

• FILE=imp021699.dmp
• LOG=exp021699.log
• COMMIT=Y

Import methods

Imports can be done in three methods:

Importing Table

If you desire only to import a table, or group of tables in a schema; You can probably write your parameter file (imptables.par) as shown below

• FILE=imptables021699.dmp
• LOG=imptables021699.log
• TABLES=(table1, table2, table3)

Importing Users

If you desire you can import all objects in a users schema (such as tables, data, grants and indexes). By default a ordinary user can only import his current schema. A user with IMP_FULL_DATABASE privilege can import other users schema.

Importing Full Database

You have to have IMP_FULL_DATABASE privilege, to carry on this operation, the objects that are imported are:

• Profiles
• Public database links
• Public synonyms
• Roles
• Rollback segment definitions
• System audit options
• System privileges
• Tablespace definitions
• Tablespace quotas
• User definitions