Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: URGENT: IMPORT HELP


From: Bobak, Mark <>
Date: Tue, 1 Aug 2006 17:34:33 -0400
Message-ID: <>


Why are you concerned about creating the PKs correctly? Are you importing from a static file? Did you have consistent=y when the export was run? Are you importing from a live database via named pipe?  

In terms of improving performance, I would:  

1.)  Disable all triggers.
2.)  Disable all FKs, then all PKs and UKs.
3.)  Mark all indexes unusable.
4.)  Run the import with indexes=n constraints=n
5.)  alter index rebuild for all unusable indexes, with nologging and optionally parallel.
6.)  Create any indexes which were dropped when PK or UK was disabled, preferably this time as non-unique (so they won't be dropped w/ future refreshes) and with nologging and optionally parallel.
7.)  If any indexes were created or built w/ parallel, careful to set the parallel degree back to 1, or you may see unexpected execution plans.
7.)  Enable all PKs and UKs.  (Should go fast, if indexes already exist.)
8 .) Enable FKs. (If you're brave, enable novalidate to make it really move.) 9.) Enable all triggers.    

Hope that helps,  


Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

Ours is the age that is proud of machines that can think and suspicious of men who try to.  --H. Mumford Jones, 1892-1980


From: [] On Behalf Of Paula Stankus Sent: Tuesday, August 01, 2006 5:19 PM To: oracle-l Subject: Re:URGENT: IMPORT HELP Using Oracle 8.1.7, Solaris 2.9 Trying to load a large amount of data using import. The schema needs to be the same and only the data reloaded. There are triggers, constraints (pk, fk, etc.) and pk indexes. I have been loading with pk's enabled and pk indexes (not fk and fk indexes) and the process is very slow. I have been running the import with statistics=n, indexes=n I am tempted to disable all constraints and drop pk indexes but I am afraid that I might have trouble creating the pk's properly. Can I used the indexfile option to create the pk's properly and will this make a significant difference in the import. What about after insert triggers? What is the best way to handle the after insert triggers? Any help would be appreciated. I have been spending a lot of time with this data load. Thanks, Paula
Groups are talking. We´re listening. Check out the handy changes to Yahoo! Groups. <**> --
Received on Tue Aug 01 2006 - 16:34:33 CDT

Original text of this message