Q
Problem solve Get help with specific problems with your technologies, process and projects.

Importing a .zl file into an Oracle database/schema

I have an Oracle file (filename.zl) that I want to import into my Oracle database/schema. Will the import utility prompt me to only import data into the existing tables? Will the .zl file overlay the existing data? Do I initiate the import utility from the command prompt?

I have an Oracle file (filename.zl) that I want to import into my Oracle database/schema.

  1. Will the import utility prompt me to only import data into the existing tables?
  2. Will the .zl file overlay the existing data?
  3. Do I initiate the import utility from the command prompt?
Oracle's import utility can be used either with a parameter file or interactively. Either way, the import utility is called from the command line (it can also be called via Oracle Enterprise Manager but I won't explain that here). When using a parameter file, the command looks something like this:
imp SYSTEM/password PARFILE=params.par
You would have created your parameter file ahead of time with the appropriate parameters. The parameter file might look similar to this:
FILE=filename.zl
SHOW=n
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=blake
TOUSER=scott
TABLES=(unit,manager)
Note that these are not the only import parameters available. You should review the Oracle9i Database Utilities Guide to familiarize yourself with the parameters. The guide can be downloaded from Oracle Technology Network by clicking here.

If you choose to use the import facility interactively, you must provide much of the same information when prompted. To use import interactively, type:

imp
at the command prompt.

I've assumed that you want to import a schema only and not an entire database. If the export is a full database export and you want to import the entire database, then you must include the full=y parameter in your parameter file and exclude the TOUSER and FROMUSER parameters as these no longer apply once you specify that you want to import the entire database. Prior to importing a schema, be sure to first create the appropriate tablespaces and the schema owner account, as well as any roles that need to be granted during the import.

With respect to your first question, there is an import parameter called IGNORE. The default setting for this parameter is N, which means that the import utility will log or display object creation errors before continuing. So if it is trying to import a table that already exists, it will report an error and continue on to the next object. If, however, you set IGNORE=Y this means that import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors. If a table already exists and IGNORE=y, then rows are imported into existing tables without any errors or messages being given.

Finally, with respect to your second question, when you import into existing tables, if no column in the table is uniquely indexed, rows could be duplicated.

Dig Deeper on Oracle database installation, upgrades and patches

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close