disable constraints before export to improve performance of import?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: disable constraints before export to improve performance of import?

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    disable constraints before export to improve performance of import?

    Hi guys.

    I'm now importing a single user into a different database. The problem is that it takes a long time and I get the feeling it's because of the constraints. I import all tables in a new user schema, so all tables are created and the data is freshly inserted. I guess the constraints are checked at every new inserted row.

    Because the referential integrity was okay in the original database, I guess the validation during the import is obsolete in this case?

    Is there a way to import the data in such a way, that the check on the constraints is postponed. The import could go faster and I could enable the constraints at night...

    The procedure/issued commands :
    - drop all tables from user APP01;
    - drop all other objects from user APP01;
    - imp system/manager@db01 FULL=N FROMUSER=APP01 TOUSER=APP01 IGNORE=Y file=c:\oracle\oradata\db01\import\db02_full.dmp


    Hmmm now checking the help-info again... can I use TOID_NOVALIDATE for this?

    Thanks in advance and have a good weekend!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Jun 2000
    Posts
    295
    Constraints should be enforced after all rows imported.

    You can import without constraints:
    imp ... CONSTRAINTS=N

    I do not know you can postpone constraints. But you
    can do:

    imp ... rows=N
    to import definitions first

    Write disable constraints to disable all constraints, then

    imp ... rows=Y ignore=y
    to import data

    Then, "enable the constraints at night"

    TOID_NOVALIDATE is referring to Type Object ID, which I
    do not think it relates to constraints.

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Great! Thank you for your answer!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

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