Skip navigation

Each time I begin working in a new project, I have to imoprt most recent data from the production environment to development environment. Then Oracle Imp exp utilities come to work.

As Oralce describe the contents of the export file The export file contains objects in the following order:

  1. Type definitions

  2. Table definitions

  3. Table data

  4. Table indexes

  5. Integrity constraints, views, procedures, and triggers

  6. Bitmap, function-based, and domain indexes

And in this order the import work:

The order of import is as follows: new tables are created, data is imported and indexes are built, triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, function-based, and/or domain indexes are built. 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 is originally inserted and again during the import)

Issues of Oracle import

From the previous order of import, the import process should work without issues. but actually this is not the case. Import always done on existing tables in the target schema.

Herein a list of issues that could occur during the migration of data (Exp Imp Process)

  1. Integrity Constraints
    Referential constraints are imported only after all tables are imported. But errors can still occur when data is loaded into existing tables. if the existing table contains referential integrity to another table that has not yet been imported.
    To solve this issue, we need just to disable referential integrity constraints on the existing tables.
  2. Triggers
    Trigger could be source of data quality issues during the import. You might think that the import has done perfectly without issues but trigger could be a risk to the data quality, try compare number of rows imported between the source and the targer schemas. One time I have discovered a duplication in number of rows in 2 tables, later I discovered that there is a trigger on another table -included in the import process- which keep inserting rows in that table, then when it come to the imp process to load this table it has added another set of rows.In order prevent this from happening, we need to disable all triggers before starting the imp and later enable it when the process fininsh.

    You can find here useful information about how to prepare a script for disabling con before importing.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: