The Database Design Resource Center

Oracle Import : Loading data into the database

Oracle Import inserts the data objects extracted from one Oracle database by the Oracle Export utility into another Oracle database. Oracle Export dump files can only be read by Import.

Oracle Import reads the object definitions and table data that the Oracle Export utility extracted from an Oracle database and stored in an Oracle binary-format Oracle Export dump file located typically on disk or tape.

Such files are transported to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.

The question which comes to mind is: How can we perform import and export functions?

To use the Oracle Import utility, prepare the import database and ensure that the import user has the proper authorizations.

Before you can use the Import utility, you must prepare the import database, as follows:

  1. Create any security policies, which protect the data to be imported. The policies must use the same column names as in the export database.
  2. Define in the import database all of the label components and individual labels used in tables being imported. Tag values assigned to the policy labels in each database must be the same.

Verifying Import User Authorizations:

To successfully import data under Oracle security, the user running the Oracle Import operation must be authorized for all of the labels required to insert the data and labels contained in the export file.

Errors will be raised upon import if the following requirements are not met:

Requirement 1:

To assure that all rows can be imported, the user must have the policy_DBA role for all policies with data being imported.

After each schema or table is imported, any policies from the export database are reapplied to the imported objects.

Requirement 2:

The user must also have the ability to write all rows that have been exported. This can be accomplished by one of the following methods:

  • The user can be granted the FULL privilege.
  • A user-defined labeling function can be applied to the table.
  • The user can be given sufficient authorization to write all labels contained in the import file.

Defining Data Labels for Import:

The label definitions at the time of import must include all of the policy labels used in the export file.

You can use the views


in the export database to design SQL scripts that re-create the label components and labels for each policy in the import database.

The following example (in SQL*Plus) shows how to generate a PL/SQL block that re-creates the individual labels for the HR policy:

set serveroutput on

(SELECT label_tag, label
FROM dba_sa_labels
WHERE policy_name='HR'
ORDER BY label_tag) LOOP
CREATE_LABEL(''HR'', ' || l.label_tag || ', ''' || l.label || ''');');
dbms_output.put_line ('END;');
dbms_output.put_line ('/');

If the individual labels do not exist in the import database with the same numeric values and the same character string representations as in the export database, then the label values in the imported tables will be meaningless.

The numeric label value in the table may refer to a different character string representation, or it may be a label value that has not been defined at all in the import database.

One of the most important issues for an Oracle administrator is tracking the execution of an Oracle import.

For very large tables, the Oracle Import utility can take many hours, and the DBA needs to know the rate at which the utility is adding rows to the table. To monitor how fast rows are imported from a running import job, try the following method.

SELECT SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea WHERE sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions NOT 0;

(Sorry about the bad formatting: I will try to do it better at a later stage...)

Return to Oracle DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.