RE: 10g upgrade survey

  • From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
  • To: JApplewhite@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 23 Mar 2007 10:07:19 -0500

Oh yeah, there is a general process you have to follow for this to work:


1.      Import all tables, rows=n
2.      Disable all fk constraints, primary key constraints, and drop
indexes, (also disable check constraints if you trust your data).
3.      Import the tables ignore=y
4.      Step 3 should have rebuilt all the indexes and enabled the fk
constraints, but verify that and then enable all the check constraints.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Friday, March 23, 2007 9:47 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: 10g upgrade survey


Yes, "Divide and Conquer" when doing Exports/Imports is a very good
strategy.  Our Student Information System has over 127,000 tables (yes,
that's One Hundred Twenty-Seven Thousand), representing seven
SchoolYears' of data.  We use SQL statements to dynamically create an
Export *.par file for each SchoolYear and export them in parallel.  It's
only about 30G of data, but parallelizing the process has greatly
speeded up the refresh of our Dev and other databases with selected

As Andrew points out, having LOBs or not impacts the Export times.  I
would add that the degree of constraints and/or indexing greatly impacts
the Import times. 

BTW, we've found that the Tables= list in the Export *.par fiile can be
somewhere just short of 32,000 before Export barfs. 

Our environment is predominantly Oracle9i on Linux and HPUX. 

Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk)  /  512.935.5929 (pager)

Same-Day Stump Grinding!  Senior Discounts!
        -- Mike's Tree Service

"Kerber, Andrew W." <Andrew.Kerber@xxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 

03/23/2007 08:05 AM 

Please respond to


ujang.jaenudin@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx 




RE: 10g upgrade survey




Single table or multiple tables?  When we had to do 300G or so, we
exported groups of tables at a time using 4 separate streams, and did
the same on import.  Took about 8 hours total for the whole thing.  Of
course, we didn't have many clobs, they can make things take a lot

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ujang Jaenudin
Sent: Friday, March 23, 2007 6:00 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: 10g upgrade survey


could anyone have experience export/import about 250GB data? on SAN
how long it takes. or is it better by dbua & cross platform
transportable tablespace?

I will migrate oracle from win2k 64 bit oracle 9i non RAC to Linux 64
bit Oracle 10g RAC.

thanks for the reply


NOTICE:  This electronic mail message and any attached files are confidential.  
The information is exclusively for the use of the individual or entity intended 
as the recipient.  If you are not the intended recipient, any use, copying, 
printing, reviewing, retention, disclosure, distribution or forwarding of the 
message or any attached file is not authorized and is strictly prohibited.  If 
you have received this electronic mail message in error, please advise the 
sender by reply electronic mail immediately and permanently delete the original 
transmission, any attachments and any copies of this message from your computer 
system. Thank you.


Other related posts: