Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Export of very big table

RE: Export of very big table

From: <eric.lansu_at_dutchtone.nl>
Date: Thu, 28 Dec 2000 14:53:35 +0100
Message-Id: <10724.125387@fatcity.com>


I know exporting with the direct-parameter is faster, but we are hopelessly trying to find a cause why we cannot import our export-file anymore. Export doesn't complain about the direct=y. I have the commit=y and analyze=n in the import. It's still running, and it has 9 Million records in the table (22 hours after the start of the import) My test-machine may not be that fast, it's only a Sun E450 while production is a fully equipped E10000.

Still I have some questions for you;

Have you imported this export (on a test-database)? How long did it take?
How big was the export-file?
Did you split it into 2Gb parts?
Have you compressed the files?
With compress or gzip?
What version are you on?

TIA. Eric Lansu

> -----Original Message-----
> From: Koivu, Lisa [SMTP:lkoivu_at_qode.com]
> Sent: Thursday, December 28, 2000 1:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Export of very big table
>
> Hi Eric,
>
> Crank your buffer size way up for your import! That's going to speed
> things up a lot. Also, by habit, when I import a large table I use
> COMMIT=Y.
>
> Exports use the direct parameter, and that will speed up your export,
> definately. Not all objects can be exported via direct - if exp can't do
> direct, it will tell you in the log file and export via conventional.
> It's no big deal. I went from exporting our 150GB database in several
> hours to under an hour using direct.
>
> Imports do not use the direct parameter.
>
> Hope this helps... good luck.
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>
> V: 954.484.3191, x174
> F: 954.484.2933
> C: 954.658.5849
> <http://www.qode.com>
>
> "The information contained herein does not express the opinion or position
> of Qode.com and cannot be attributed to or made binding upon Qode.com."
>
>
> -----Original Message-----
> From: eric.lansu_at_dutchtone.nl [ <mailto:eric.lansu_at_dutchtone.nl>]
> Sent: Thursday, December 28, 2000 1:30 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Export of very big table
>
>
> Hello and good-morning (It is for me!)
>
> I am using Rachel's scripts now too. The difference could be in the
> omission
> of the 'direct' parameter while exporting. Usually we use it, but they are
>
> not in Rachel's script.
> I cannot see if it works yet, for I exported 19.000.000 rows yesterday and
>
> started to import them at 16:00. Now it's 7:30 (Here in Holland) and it
> only
> imported 7.000.000 rows up till now.... (15h30m 7.000.000 => 42h import
> time
> :o(
> This must be caused by 1. The omission of the direct-parameter 2. A to
> small buffer (4Mb)
> There's a lot of testing to be done, but it takes a lot of time....
>
> Een Heel Gelukkig Nieuwjaar
> A Very Happy New Year
>
> Eric Lansu, BEST-IT
> ORACLE DBA
> DutchTone - IS (Cap Gemini)
> E-mail : *eric.lansu_at_dutchtone.nl
>
> It's time for the www.best.nl in you
>
> > The information contained in this communication is confidential and may
> be
> > legally privileged. It is intended solely for the use of the individual
> or
> > entity to whom it is addressed and others authorised to receive it. If
> you
> > are not the intended recipient you are hereby notified that any
> > disclosure, copying, distribution or taking any action in reliance on
> the
> > contents of this information is strictly prohibited and may be unlawful.
>
> > Dutchtone is neither liable for the proper and complete transmission of
> > the information contained in this communication nor for any delay in its
>
> > receipt.
> >
> >
> >
> > -----Original Message-----
> > From: Ruth Gramolini [SMTP:rgramolini_at_tax.state.vt.us]
> > Sent: Tuesday, December 26, 2000 4:31 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Export of very big table
> >
> > Did you try the export/import written by Rachel Camichael? They create
> a
> > file or files of 2 gig. They can be found on the LazyDBA website under
> > scripts. I am using them to import as we speak.
> >
> > HTH,
> > Ruth
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Friday, December 22, 2000 6:30 AM
> >
> >
> > > Hi listers,
> > >
> > > I've been off the list for some time, but I'm back with a nice
> problem!
> > Hope
> > > to resume helping you with your's too.
> > >
> > > I have a database with tables of 10Gb. Creating the export-file fails,
>
> > for
> > > Oracle has a problem creating a export-file with a compressed size of
> > 1.5
> > Gb
> > > and more.
> > > Oracle suggested to use a named pipe with the unix-split command, but
> > this
> > > doesn't work either. We can use the method with the named pipe for an
> > > export-file with an uncompressed file size of 2Gb up to compressed
> > 1.5Gb,
> > > but splitting it doesn't work.
> > > It's an Oracle 7 database, so we cannot use the 8 functionality of exp
>
> > to
> > > split the dump over more datafiles :-(
> > >
> > > Help is very much appreciated, I will be in for 2.5 hours today, and
> > then
> > > Wednesday again.
> > >
> > > Prettige Kerst en een Gelukkig Nieuwjaar
> > > Merry Christmas and a Happy New Year
> > >
> > > Eric Lansu, BEST-IT
> > > ORACLE DBA
> > > DutchTone - IS (Cap Gemini)
> > >
> >
> --
> Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
> --
> Author:
> INET: eric.lansu_at_dutchtone.nl
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Thu Dec 28 2000 - 07:53:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US