Import contraints question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Import contraints question

  1. #1
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110

    Import contraints question

    I do a 35G data import. Importing the data is Ok. but 80% of the time is spent on enabling constraints..I have 6000 constaints.
    I would like to do a constraints=n import and then enable the constraints novalidate
    Question. If I do that, would the constraints be created? just not validated/enabled?
    Thanks
    Looking for the greatest evil in the world? Look in the mirror.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are correct.
    Import with no constraints then create constraints enable/novalidate which means you trust the data already in the tables but you want Oracle to check constraints from now on.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Quote Originally Posted by Tuma View Post
    I do a 35G data import. Importing the data is Ok. but 80% of the time is spent on enabling constraints..I have 6000 constaints.
    I would like to do a constraints=n import and then enable the constraints novalidate
    Question. If I do that, would the constraints be created? just not validated/enabled?
    Thanks
    Try it then query to see if the constraints are there. I imagine that the import won't create the constraints if you say constraint=n on the import command. A better option would be to use transportable tablespaces. That is assuming that your situation qualifies. You basically need each tablespace to have only constraints to objects within that tablespace and you need to move the tablespace to a file system that is compatible with were you took it from. But if it works it would not need to reenable all of the constraints.

    Another option would be to use dbms_metadata to get the ddl for the constraints, and edit that ddl to make it create the constraints with the novalidate flag. You can then run that after the import to create the constraints. Just make sure that the constraints are valid on the source database.
    this space intentionally left blank

  4. #4
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    thanks fellows...
    Looking for the greatest evil in the world? Look in the mirror.

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