RE: Data load options

From: Muthu Avudaiyappan <mavudaiyappan_at_vertek.com>
Date: Thu, 26 Jul 2001 13:55:30 -0700
Message-ID: <F001.00356854.20010726133704@fatcity.com>

Hi All

I have a Similar Question

Background -- When migrating from a Proprietary P4 Database to Oracle 8.05 Database it blows up to 2 - 3 times of the data file size which is in the P4 database.
We only have the proprietary interface for Migration.We donot have any control over the storage clause or any thing.

What are the step to be taken under consideration for Oracle as far as BULK LOADING goes. like PCT FREE to 0, NOT to be in the archive log mode etc..etc...

Please advice......

Thanks
Muthu

> -----Original Message-----
> From: Rachel Carmichael
> Sent: Thursday, July 26, 2001 2:12 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: Data load options
>
> 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>
> > >
> > > Hi Sean, 
> > > 
> > >
> > > Others have given you
> > > different
> > > options.  Here's some considerations:
> > > 
> > >
> > > 1.  SQL Script.
> >
> > >
> > > <UL><LI>Can be SLOW
> ></LI>
> > > <LI>May require intermittent
> > > commits
> > > in your script</LI>
> > > <LI>Manual and error
> > > prone</LI>
> > > <LI>Must go through SQL
> > > engine</LI>
> > > 
> > > </UL>
> > > 2.  SQL
> Loader
> > >
> > > <UL><LI>Can be very fast
> >(direct
> > > -
> > > bypassing SQL engine). </LI>
> > > <LI>Direct path load will
> > > invalidate
> > > indexes. </LI>
> > > <LI>You can keep track of
> > > rejected
> > > records easily (.bad file)</LI>
> > > <LI>Dependent upon your file
> > > format
> > > being EXACT, no errors.</LI>
> > > 
> > > </UL>
> > > 3.  Import
> > >
> > > <UL><LI>Requires minimal
> >manual
> > > fiddling</LI>
> > > <LI>Can be very slow - I
> once
> > > imported
> > > 150GB and it took ~3 days</LI>
> > > <LI>Does not seamlessly
> handle
> > > all
> > > object types (INtermedia (domain) indexes is one example)</LI>
> > > <LI>Restarting an import
> will
> > > take a
> > > lot longer.  If your import fails, be sure and truncate/drop all
> > > tables
> > > before starting again. </LI>
> > > <LI>Have the option of not
> > > including
> > > indexes (INDEXES=N)</LI>
> > > <LI>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)</LI>
> > > 
> > > </UL>
> > > 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
> > > 
> > > 
> > > <UL>
> > > -----Original Message-----
> > > From:   <FONT
> >SIZE=1
> > > FACE="Arial">O'Neill, Sean [SMTP:Sean.ONeill_at_organon.ie]
> > > Sent:   <FONT
> >SIZE=1
> > > FACE="Arial">Wednesday, July 25, 2001 6:16 AM
> > > FACE="Arial">To:    
> > > <FONT
> > > SIZE=1 FACE="Arial">Multiple recipients of list ORACLE-L
> > > > >
> >FACE="Arial">Subject:       </B
> >
> > > <FONT
> > > SIZE=1 FACE="Arial">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: <A
> > > HREF="http://groups.yahoo.com/group/Oracle-OCP-DBA"
> > >
> TARGET="_blank">http://groups.yahoo.com/group/Oracle-OCP-DBA</A>
> > > 
> > >
> > > "Nobody loves me but my mother...
> and
> > > she
> > > could be jivin' too."  - BB King
> > > 
> > >
> > > -- 
> > > Please see the official ORACLE-L FAQ: <A
> > > HREF="http://www.orafaq.com"
> > > TARGET="_blank">http://www.orafaq.com</A>
> > > -- 
> > > 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
> > > > >
> >FACE="Arial">------------------------------------------------------------
> -
> > > ------
> > > -
> > > 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).
> > > 
> > > </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)..

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Muthu Avudaiyappan
  INET: mavudaiyappan_at_vertek.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 - 15:55:30 CDT