Oracle Import : Loading data into the database

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

DBA_SA_LEVELS,
DBA_SA_COMPARTMENTS,
DBA_SA_GROUPS, and
DBA_SA_LABELS

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

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

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