HELP: Schemas's circular reference problem using IMPORT
Hi friends, I'm here with a new doubt.
I'm trying to import some schemas using the import utility (imp) to a new database. I do not have the original database because is already dead by a hardware problem , so the logical backup is the only information that I have.
When I try to import information from the squema1, it references objects in the schema2, and viceversa! I have found a lot of dependencies via the IMP utility between my set of schemas (many schemas and many circular references) and I'm sure that there is not a way to importing them in any order that can successfuly upload this information without either warnings nor errors.
Do anybody know how to import the information of schemas avoiding errors in order to upload the information successfuly ? I think the import utility would know the dependence order and upload the information in this order ... But I do not know how to do it.
If any of you has dealed with a similar problem, plese don't doubt in answer me. I will really appreciate your support.
You can use the import with the indexfile= option which will give you all the table and index creation statements for all schemas in the export file. Hack the script so that it places objects in the correct schemas/tablespaces and then run the import ignore=Y and it will work like a dream.
remember where ever you are, that is where you will find yourself...
what error do u get on import? oracle imports *in order* and enables things like constraints after the object have been created so in that sense you dependencies are taken care of, most importantlt please post the errors you receive on import
Originally posted by Turin Or the import problem when user1 grants privilege to user2 and user2 has not been created yet. And I want to create these privileges, what do I need to do ?
Any ideas ?
should not be an issue, have you read the utilities guide?
Table Objects: Order of Import
Table objects are imported as they are read from the export file. The export file contains objects in the following order:
Integrity constraints, views, procedures, and triggers
Bitmap, functional, and domain indexes
First, new tables are created. Then, data is imported and indexes are built. Then triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, functional, and/or domain indexes are built. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data (once when it was originally inserted and again during the import).
For example, if the EMP table has a referential integrity constraint on the DEPT table and the EMP table is imported first, all EMP rows that reference departments that have not yet been imported into DEPT would be rejected if the constraints were enabled.
When data is imported into existing tables, however, the order of import can still produce referential integrity failures. In the situation just given, if the EMP table already existed and referential integrity constraints were in force, many rows could be rejected.
A similar situation occurs when a referential integrity constraint on a table references itself. For example, if SCOTT's manager in the EMP table is DRAKE, and DRAKE's row has not yet been loaded, SCOTT's row will fail, even though it would be valid at the end of the import.
Suggestion: For the reasons mentioned previously, it is a good idea to disable referential constraints when importing into an existing table. You can then reenable the constraints after the import is completed.