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: RE: Data load options

RE: RE: Data load options

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Thu, 26 Jul 2001 11:06:55 -0700
Message-ID: <F001.00356376.20010726101144@fatcity.com>

Okay Lisa,

WHY were you on painkillers?

Rachel

>From: "Koivu, Lisa" <lisa.koivu_at_efairfield.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: RE: Data load options
>Date: Thu, 26 Jul 2001 07:47:06 -0800
>
>Oh yes. You are right. It loads full blocks above the HWM. Forgot to
>mention that... thanks for pointing out that VERY important bit of info.
>Must be the narcotics.
>
>Lisa
>
> > -----Original Message-----
> > From: dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com]
> > Sent: Wednesday, July 25, 2001 11:03 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re:RE: Data load options
> >
> > Lisa,
> >
> > SQL*Loader in direct path can cause you storage problems since it
> > always
> > appends to the table(s) ignoring blocks on the free block list. I found
> > that
> > out after a developer was using it for our data warehouse. At the end
>of
> > a year
> > he had burned almost 100GB of disk & it kept increasing even though he
>was
> > trying to keep only a rolling 180 window. On a hunch we exported the
> > data,
> > truncated the table & imported the data back in. The amount of
>tablespace
> > being
> > used dropped 50%.
> >
> > Dick Goulet
> >
> > ____________________Reply Separator____________________
> > Author: "Koivu; Lisa" <lisa.koivu_at_efairfield.com>
> > Date: 7/25/2001 6:26 AM
> >
> > Hi Sean,
> >
> > Others have given you different options. Here's some considerations:
> >
> > 1. SQL Script.
> > * Can be SLOW
> > * May require intermittent commits in your script
> > * Manual and error prone
> > * Must go through SQL engine
> >
> > 2. SQL Loader
> > * Can be very fast (direct - bypassing SQL engine).
> > * Direct path load will invalidate indexes.
> > * You can keep track of rejected records easily (.bad file)
> > * Dependent upon your file format being EXACT, no errors.
> >
> > 3. Import
> > * Requires minimal manual fiddling
> > * Can be very slow - I once imported 150GB and it took ~3 days
> > * Does not seamlessly handle all object types (INtermedia (domain)
> > indexes is one example)
> > * Restarting an import will take a lot longer. If your import
> > fails,
> > be sure and truncate/drop all tables before starting again.
> > * Have the option of not including indexes (INDEXES=N)
> > * Can also reset your storage parms prior to import (export with
> > data=n, edit file [SSSHHH] and enter new storage parms / import, export
> > with
> > data=y, import data with IGNORE=Y)
> >
> > I'm sure others will have additional considerations to share. My choice
> > has
> > always been exp/imp.
> >
> > Lisa Koivu
> > The Vicodin-enhanced DBA
> > Ft. Lauderdale, FL, USA
> >
> > > -----Original Message-----
> > > From: O'Neill, Sean [SMTP:Sean.ONeill_at_organon.ie]
> > > Sent: Wednesday, July 25, 2001 6:16 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Data load options
> > >
> > > I'm trying to compile a list of options for loading data into an
>Oracle
> > > database on NT platform. What I've come up with so far is:
> > > [1] SQL script that performs inserts, updates.
> > > [2] SQL Loader utility
> > > [3] Import utility
> > >
> > > Are there others?
> > > Anyone care to share experience based opinions on pros and cons of the
> > > methods?
> > >
> > >
> > > Sean :)
> > >
> > > Rookie Data Base Administrator
> > > [0%] OCP Oracle8i DBA
> > > [0%] OCP Oracle9i DBA
> > > -------------------------------- ------------
> > > Organon (Ireland) Ltd.
> > > E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]
> > >
> > > Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
> > >
> > > "Nobody loves me but my mother... and she could be jivin' too." - BB
> > King
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: O'Neill, Sean
> > > INET: Sean.ONeill_at_organon.ie
> > >
> > > 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).
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
> > <HTML>
> > <HEAD>
> > <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
> > <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
> > <TITLE>RE: Data load options</TITLE>
> > </HEAD>
> > <BODY>
> >
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Hi Sean, </FONT>
> > </P>
> >
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Others have given you
> > different
> > options.&nbsp; Here's some considerations:</FONT>
> > </P>
> >
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">1.&nbsp; SQL Script.
></FONT>
> >
> > <UL><LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Can be SLOW
></FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">May require intermittent
> > commits
> > in your script</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Manual and error
> > prone</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Must go through SQL
> > engine</FONT></LI>
> > <BR>
> > </UL>
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">2.&nbsp; SQL Loader</FONT>
> >
> > <UL><LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Can be very fast
>(direct
> > -
> > bypassing SQL engine). </FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Direct path load will
> > invalidate
> > indexes. </FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">You can keep track of
> > rejected
> > records easily (.bad file)</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Dependent upon your file
> > format
> > being EXACT, no errors.</FONT></LI>
> > <BR>
> > </UL>
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">3.&nbsp; Import</FONT>
> >
> > <UL><LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Requires minimal
>manual
> > fiddling</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Can be very slow - I once
> > imported
> > 150GB and it took ~3 days</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Does not seamlessly handle
> > all
> > object types (INtermedia (domain) indexes is one example)</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Restarting an import will
> > take a
> > lot longer.&nbsp; If your import fails, be sure and truncate/drop all
> > tables
> > before starting again. </FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Have the option of not
> > including
> > indexes (INDEXES=N)</FONT></LI>
> > <LI><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Can also reset your
>storage
> > parms
> > prior to import (export with data=n, edit file [SSSHHH] and enter new
> > storage
> > parms / import, export with data=y, import data with
>IGNORE=Y)</FONT></LI>
> > <BR>
> > </UL>
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">I'm sure others will have
> > additional considerations to share.&nbsp; My choice has always been
> > exp/imp.
> > </FONT>
> > </P>
> >
> > <P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Lisa Koivu</FONT>
> > <BR><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">The Vicodin-enhanced
> > DBA</FONT>
> > <BR><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Ft. Lauderdale, FL, USA
> > </FONT>
> > </P>
> > <UL>
> > <P><FONT SIZE=1 FACE="Arial">-----Original Message-----</FONT>
> > <BR><B><FONT SIZE=1 FACE="Arial">From:&nbsp;&nbsp;</FONT></B> <FONT
>SIZE=1
> > FACE="Arial">O'Neill, Sean [SMTP:Sean.ONeill_at_organon.ie]</FONT>
> > <BR><B><FONT SIZE=1 FACE="Arial">Sent:&nbsp;&nbsp;</FONT></B> <FONT
>SIZE=1
> > FACE="Arial">Wednesday, July 25, 2001 6:16 AM</FONT>
> > <BR><B><FONT SIZE=1 FACE="Arial">To:&nbsp;&nbsp;&nbsp;&nbsp;</FONT></B>
> > <FONT
> > SIZE=1 FACE="Arial">Multiple recipients of list ORACLE-L</FONT>
> > <BR><B><FONT SIZE=1
> >
>FACE="Arial">Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</FONT></B>
> > <FONT
> > SIZE=1 FACE="Arial">Data load options</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">I'm trying to compile a list of options for
> > loading
> > data into an Oracle</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">database on NT platform.&nbsp; What I've
> > come up
> > with so far is:</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">[1] SQL script that performs inserts,
> > updates.</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">[2] SQL Loader utility</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">[3] Import utility</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">Are there others?</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">Anyone care to share experience based
> > opinions on
> > pros and cons of the</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">methods?</FONT>
> > </P>
> > <BR>
> >
> > <P><FONT SIZE=2 FACE="Arial">Sean :)</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">Rookie Data Base Administrator</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">[0%] OCP Oracle8i DBA</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">[0%] OCP Oracle9i DBA</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">--------------------------------
> > ------------
> > </FONT>
> > <BR><FONT SIZE=2 FACE="Arial">Organon (Ireland) Ltd.</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">E-mail: sean.oneill_at_organon.ie&nbsp;&nbsp;
> > [subscribed: Digest Mode]</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">Visit: <A
> > HREF="http://groups.yahoo.com/group/Oracle-OCP-DBA"
> > TARGET="_blank">http://groups.yahoo.com/group/Oracle-OCP-DBA</A></FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">&quot;Nobody loves me but my mother... and
> > she
> > could be jivin' too.&quot;&nbsp; - BB King</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">-- </FONT>
> > <BR><FONT SIZE=2 FACE="Arial">Please see the official ORACLE-L FAQ: <A
> > HREF="http://www.orafaq.com"
> > TARGET="_blank">http://www.orafaq.com</A></FONT>
> > <BR><FONT SIZE=2 FACE="Arial">-- </FONT>
> > <BR><FONT SIZE=2 FACE="Arial">Author: O'Neill, Sean</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">&nbsp; INET: Sean.ONeill_at_organon.ie</FONT>
> > </P>
> >
> > <P><FONT SIZE=2 FACE="Arial">Fat City Network Services&nbsp;&nbsp;&nbsp;
> > --
> > (858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">San Diego,
> > California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet
> > access /
> > Mailing Lists</FONT>
> > <BR><FONT SIZE=2
> >
>FACE="Arial">-------------------------------------------------------------
> > ------
> > -</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">To REMOVE yourself from this mailing list,
> > send an
> > E-Mail message</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">to: ListGuru_at_fatcity.com (note EXACT
> > spelling of
> > 'ListGuru') and in</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">the message BODY, include a line
>containing:
> > UNSUB
> > ORACLE-L</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">(or the name of mailing list you want to
>be
> > removed from).&nbsp; You may</FONT>
> > <BR><FONT SIZE=2 FACE="Arial">also send the HELP command for other
> > information
> > (like subscribing).</FONT>
> > </P>
> > </UL>
> > </BODY>
> > </HTML>
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: dgoulet_at_vicr.com
> >
> > 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).



Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.com

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).
Received on Thu Jul 26 2001 - 13:06:55 CDT

Original text of this message

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