-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|