HELP: Schemas's circular reference problem using IMPORT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: HELP: Schemas's circular reference problem using IMPORT

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    Exclamation 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.

    Thanks in advance

  2. #2
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    If you do imp help=y and readup the help
    it talks about ignore option. set ignore=yes along with your imp statement..

    ie: imp scott ignore=y file=foo.dmp etc...

    so even if you get errors from your imp Ignore=yes specifies to ignore the errors and continue with the import.
    Hope this helps

    -Jigar
    "High Salaries = Happiness = Project Success."

  3. #3
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    But what happend in this case?

    The user1 creates a trigger on the user2 schema and the import first restores the user1 schema so the triggers cannot be created on tables of schema2 ...

    Regards

  4. #4
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    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 ?

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    IMPORT INDEXFILE=/.../...

    CREATE THE SCHEMAS MANUALLY, AND GRANT PRIVILEGE

    IMPORT IGNORE=Y
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #6
    Join Date
    Jan 2003
    Posts
    1
    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.

    GOOD LUCK,

    remember where ever you are, that is where you will find yourself...

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

    steve
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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 ?

    Thanks
    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:

    Type definitions

    Table definitions

    Table data

    Table indexes

    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.
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width