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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED] [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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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. Here's some considerations:</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">1. 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. 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. 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. 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. 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: </FONT></B> <FONT SIZE=1
FACE="Arial">O'Neill, Sean [SMTP:[EMAIL PROTECTED]]</FONT>
<BR><B><FONT SIZE=1 FACE="Arial">Sent: </FONT></B> <FONT SIZE=1
FACE="Arial">Wednesday, July 25, 2001 6:16 AM</FONT>
<BR><B><FONT SIZE=1 FACE="Arial">To: </FONT></B> <FONT
SIZE=1 FACE="Arial">Multiple recipients of list ORACLE-L</FONT>
<BR><B><FONT SIZE=1
FACE="Arial">Subject: </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. 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: [EMAIL PROTECTED]
[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">"Nobody loves me but my mother... and she
could be jivin' too." - 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"> INET: [EMAIL PROTECTED]</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Fat City Network Services --
(858) 538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2 FACE="Arial">San Diego,
California -- 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: [EMAIL PROTECTED] (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). 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
