| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Export of very big table
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C070CA.42C7FCE2
Content-Type: text/plain
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
"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 (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C070CA.42C7FCE2 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3DUS-ASCII"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: Export of very big table</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Hi Eric, </FONT> </P> <P><FONT SIZE=3D2>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=3DY.</FONT></P> <P><FONT SIZE=3D2>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.</FONT></P> <P><FONT SIZE=3D2>Imports do not use the direct parameter.</FONT> </P> <P><FONT SIZE=3D2>Hope this helps... good luck.</FONT> </P> <P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT> <BR><FONT SIZE=3D2>Oracle Database Administrator</FONT> <BR><FONT SIZE=3D2>Qode.com</FONT> <BR><FONT SIZE=3D2>4850 North State Road 7</FONT> <BR><FONT SIZE=3D2>Suite G104</FONT> <BR><FONT SIZE=3D2>Fort Lauderdale, FL 33319</FONT> </P> <P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT> <BR><FONT SIZE=3D2>F: 954.484.2933 </FONT> <BR><FONT SIZE=3D2>C: 954.658.5849</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A></FONT> </P> <P><FONT SIZE=3D2>"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."</FONT></P> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: eric.lansu_at_dutchtone.nl [<A = HREF=3D"mailto:eric.lansu_at_dutchtone.nl">mailto:eric.lansu_at_dutchtone.nl</= A>]</FONT> <BR><FONT SIZE=3D2>Sent: Thursday, December 28, 2000 1:30 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: Export of very big table</FONT> </P> <BR> <P><FONT SIZE=3D2>Hello and good-morning (It is for me!)</FONT> </P> <P><FONT SIZE=3D2>I am using Rachel's scripts now too. The difference = could be in the omission</FONT> <BR><FONT SIZE=3D2>of the 'direct' parameter while exporting. Usually = we use it, but they are</FONT> <BR><FONT SIZE=3D2>not in Rachel's script.</FONT> <BR><FONT SIZE=3D2>I cannot see if it works yet, for I exported = 19.000.000 rows yesterday and</FONT> <BR><FONT SIZE=3D2>started to import them at 16:00. Now it's 7:30 (Here = in Holland) and it only</FONT> <BR><FONT SIZE=3D2>imported 7.000.000 rows up till now.... (15h30m = 7.000.000 =3D> 42h import time</FONT> <BR><FONT SIZE=3D2>:o(</FONT> <BR><FONT SIZE=3D2>This must be caused by 1. The omission of the = direct-parameter 2. A to</FONT> <BR><FONT SIZE=3D2>small buffer (4Mb)</FONT> <BR><FONT SIZE=3D2>There's a lot of testing to be done, but it takes a = lot of time....</FONT> </P> <P><FONT SIZE=3D2>Een Heel Gelukkig Nieuwjaar</FONT> <BR><FONT SIZE=3D2>A Very Happy New Year</FONT> </P> <P><FONT SIZE=3D2>Eric Lansu, BEST-IT</FONT> <BR><FONT SIZE=3D2>ORACLE DBA</FONT> <BR><FONT SIZE=3D2>DutchTone - IS (Cap Gemini)</FONT> <BR><FONT SIZE=3D2>E-mail : = *eric.lansu_at_dutchtone.nl</FONT> </P> <P><FONT SIZE=3D2>It's time for the www.best.nl in you</FONT> </P> <P><FONT SIZE=3D2>> The information contained in this communication = is confidential and may be</FONT> <BR><FONT SIZE=3D2>> legally privileged. It is intended solely for = the use of the individual or</FONT> <BR><FONT SIZE=3D2>> entity to whom it is addressed and others = authorised to receive it. If you</FONT> <BR><FONT SIZE=3D2>> are not the intended recipient you are hereby = notified that any</FONT> <BR><FONT SIZE=3D2>> disclosure, copying, distribution or = taking any action in reliance on the</FONT> <BR><FONT SIZE=3D2>> contents of this information is strictly = prohibited and may be unlawful.</FONT> <BR><FONT SIZE=3D2>> Dutchtone is neither liable for the proper and = complete transmission of</FONT> <BR><FONT SIZE=3D2>> the information contained in this communication = nor for any delay in its</FONT> <BR><FONT SIZE=3D2>> receipt.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> From: Ruth Gramolini = [SMTP:rgramolini_at_tax.state.vt.us]</FONT> <BR><FONT SIZE=3D2>> Sent: Tuesday, December 26, 2000 4:31 PM</FONT> <BR><FONT SIZE=3D2>> To: Multiple recipients of list = ORACLE-L</FONT> <BR><FONT SIZE=3D2>> Subject: Re: = Export of very big table</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Did you try the export/import written by Rachel = Camichael? They create a</FONT> <BR><FONT SIZE=3D2>> file or files of 2 gig. They can be found = on the LazyDBA website under</FONT> <BR><FONT SIZE=3D2>> scripts. I am using them to import as we = speak.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> HTH,</FONT> <BR><FONT SIZE=3D2>> Ruth</FONT> <BR><FONT SIZE=3D2>> ----- Original Message -----</FONT> <BR><FONT SIZE=3D2>> To: "Multiple recipients of list = ORACLE-L" <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>> Sent: Friday, December 22, 2000 6:30 AM</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> > Hi listers,</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> > I've been off the list for some time, but = I'm back with a nice problem!</FONT> <BR><FONT SIZE=3D2>> Hope</FONT> <BR><FONT SIZE=3D2>> > to resume helping you with your's = too.</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> > I have a database with tables of 10Gb. = Creating the export-file fails,</FONT> <BR><FONT SIZE=3D2>> for</FONT> <BR><FONT SIZE=3D2>> > Oracle has a problem creating a = export-file with a compressed size of</FONT> <BR><FONT SIZE=3D2>> 1.5</FONT> <BR><FONT SIZE=3D2>> Gb</FONT> <BR><FONT SIZE=3D2>> > and more.</FONT> <BR><FONT SIZE=3D2>> > Oracle suggested to use a named pipe with = the unix-split command, but</FONT> <BR><FONT SIZE=3D2>> this</FONT> <BR><FONT SIZE=3D2>> > doesn't work either. We can use the method = with the named pipe for an</FONT> <BR><FONT SIZE=3D2>> > export-file with an uncompressed file size = of 2Gb up to compressed</FONT> <BR><FONT SIZE=3D2>> 1.5Gb,</FONT> <BR><FONT SIZE=3D2>> > but splitting it doesn't work.</FONT> <BR><FONT SIZE=3D2>> > It's an Oracle 7 database, so we cannot = use the 8 functionality of exp</FONT> <BR><FONT SIZE=3D2>> to</FONT> <BR><FONT SIZE=3D2>> > split the dump over more datafiles = :-(</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> > Help is very much appreciated, I will be = in for 2.5 hours today, and</FONT> <BR><FONT SIZE=3D2>> then</FONT> <BR><FONT SIZE=3D2>> > Wednesday again.</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> > Prettige Kerst en een Gelukkig = Nieuwjaar</FONT> <BR><FONT SIZE=3D2>> > Merry Christmas and a Happy New = Year</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> > Eric Lansu, BEST-IT</FONT> <BR><FONT SIZE=3D2>> > ORACLE DBA</FONT> <BR><FONT SIZE=3D2>> > DutchTone - IS (Cap Gemini)</FONT> <BR><FONT SIZE=3D2>> ></FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: </FONT> <BR><FONT SIZE=3D2> INET: eric.lansu_at_dutchtone.nl</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT>Received on Thu Dec 28 2000 - 06:32:37 CST
 |
 |