Oracle Import : Loading data into the databaseOracle 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:
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:
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.
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:
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
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
(Sorry about the bad formatting: I will try to do it better at a later stage...)
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.