Showing results for 
Search instead for 
Did you mean: 

How to import a FULL DB export??

Enrico Venturi
Super Advisor

How to import a FULL DB export??

Hello colleagues,
I want to perform an export of my database in ORACLE release 8.1.6 and to import it in a database in release
I use the exp / imp oracle utilities.
I perform a full export (FULL=Y).
When I try to import it I get a tons of errors because the objects already exist... Yes, forgot to say that the database destination is already created and "configured".
My question is: how can I force the full import overwriting the existing data structures (user data + system data, i.e. + DB schema) ?
Alternatively: what can I do to reset the destination DB and to remove the existing objects ?? I tried to drop the table but some objects still exist; I tried to drop the tablespae but I got several error because of several constraints (rollback segment in use, .... )

Thanks a lot
Honored Contributor

Re: How to import a FULL DB export??

Since the database versions are different, i would not import any of the system objects.

If your objects already exists, try:
IMP userid=x/y full=y IGNORE=y

NB. DDL or DML against the data dictionary is among the set of things that are always really bad ideas.

If you ever feel tempted to touch anything owned by SYS or SYSTEM stop, don't, do something else

if you need any further help, let us know.

best regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: How to import a FULL DB export??

Hello again,
I got a lot of error "ORA-00001: unique constraint (SNML.PK_ASAP_ASAPID) violated", "ORA-02264: name already used by an existing constraint", and sometimes "ORA-01658: ...."

Maybe there's something wrong in the export phase?!?
Honored Contributor

Re: How to import a FULL DB export??

hi again,


But then you must make sure that all the objects already existed before the import.

You may wish to create the objects first before importing the data.

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: How to import a FULL DB export??

The big problem is that the object exported/imported are in a father-son relationship, therefore the son can be created if and only if the parent does exist!
When the export is done the order is the alphabetical one; the same happens during the import; unfortunately the first object imported, the AU4CTP, is the son of the NE, which is imported just after several steps.
The export/import should follow a pre-defined order, which is the order imposed by the integrity constraints. "ORA-02291: integrity constraint (SNML.FK_TPTRAN_TPID) violated - parent key not found"
Finally, my question is: how can I change/impose a certain order for the export/import to avoid the ORA-02291 error???

Thanks a lot

Re: How to import a FULL DB export??


Step 1. Make sure that your export is taken with the parameter CONSISTENT=Y
exp user/passwd full=Y consistent=Y ....

Step 2. After a import failure, before the second/next attempt, TRUNCATE all those tables with data OR re-create those tables.

A clean method is to drop the schema and prestage it before the next attempt.

Step 3. Use IGNORE=Y with import
imp user/passwd ignore=Y ....
Michael Schulte zur Sur
Honored Contributor

Re: How to import a FULL DB export??

Hi Enrico,

dropping a tablespace wasnt the right idea.
drop user blabla cascade;
recreate the user and the do the import.
Since no objects from that user would exist, you shouldnt get error messages. But as the others said, set ignore.


Indira Aramandla
Honored Contributor

Re: How to import a FULL DB export??

Hi Enrico,

To add to all the above good suggestions,

When you export from an earlier version of database and import into a later version of the database, you can import only the schema's with all the data and attributes across.

1. Create the database in the new version.

2. Create the new tablespaces where schema data will be stored.

3. create the users and assign the default tablespaces and quotas.

4. import the schema from the export dump file using
imp username/password fromuser= touser= commit=y buffer=..............

5. if you had pre-created the empty objects (tables, indexes, constraints, triggers.....) then in the import cammand use
imp username/password fromuser= touser= ignore=y commit=y buffer=10200.....
Not: make sure you schema tables are empty, other wise truncate them.

When you created the empty schema / user you can pre-create the objects by importing as the above import command in step 4 as
imp username/password fromuser= touser= rows=n and this will create empty objects. Then next time you can import with rows = y (default) and by giving ignore = y import utility will ignore the table creation errors as the object already exists.

6. if you have more than one schema, import each schema at a time by changing the fromuser and touser names.

BY doing this you will not change and of the sys and system objects. This will be safe.

Note: If you have a large database and many tables in different schemas, then you can export individual schema's and import them similatnously.

I hope this helps.

Never give up, Keep Trying

Re: How to import a FULL DB export??


Will the destination database be used only for this application, or does it contain other applications? If the destination db will be used for this appliction only, I would try to do a full import (FULL=Y). There should be no trouble with the sys or system objects, these objects is already created when you created the 8.1.7 DB. Indeed, they are always created as part of the CREATE DATABASE command.

You said that the database is already "created and configured", and I'm assuming that this means that some tables and other objects are already created. There should be no need for this since the objects are in the export file already and will be created during the import.

Thus you should start with an empty database.

You get the import errors because you have already created some of the objects (ORA-02264) and populated some of the tables with rows (ORA-00001). You get ORA-01658 error when you try to create an object and there is not enough space in the tablespace to create the initial extent. If you did export with COMPRESS=Y, the objects initial extent will be as large as the entire table during import (it is summed already in the export file). You may need to recreate the tablespace in order to make such a large extent fit. Even if you did an export with COMPRESS=N, this error could have something to do with the objects alredy created in the database.

If the destination database is used only for this application i would:
1. Scratch the database (CREATE DATABASE)
You do not need to create the additional tablespaces (rollback, user_data etc...). They will be created when you import the database. If you changed the layout of the tablespaces (changed drives, paths or sizes) you need to create them now. Creating the temporary tablespace can be a good idea if you are importing big indexes. Don't forget to change the system user if you create the temporary tablespace. The same goes for rollback if you import big tables.
2. Run a full import (FULL=Y)
If you created any tablespace you need to set IGNORE=Y.
Regular Advisor

Re: How to import a FULL DB export??


This is my personal experience:
I did that before many times.
If you set properly your need, you don' worry about error messages.
Why, when you create a new DB Oracle install the basic objects which are belong to sys, drsys, etc... That is why when you do a full import from full export you get errors because these objects are already installed.

Above apply with the follow step:
1. Create a new database (try to set the same size from your original database to system, tools, users, etc tablespaces)
2. Create tablespaces acording the original database.
3. Create with the same name the user application owners and belongs them to apropiate tablespace
4. Do a full import: imp system/xx file=x1.dmp log=x1.log full=y

Many errors come from the Oracle basic struture and the others come from views and some procedures and triggers.

After import, you must run /rdbms/ultrp fro sqlplus and check via enterprise manager any invalid object.

I did that many times, between diferent versions, ie: 7.3.4 to 8.0.5 or 8.0.5 to 8.1.7.
After years, all still works fine...