If you have an initial and next extent set correctly and do not compress on
export then there is no problem on import. One of the most common import
problem is when an export is carried out with compress=y and then there is
insufficient contiguous space for the initial extent upon import. In this
situation it is necessary to precreate the segment prior to the import.
> -----Original Message-----
> From: Jain, Akshay [SMTP:Akshay.Jain_at_cit.com]
> Sent: Wednesday, April 26, 2000 6:27 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Tablespace reorg tools
>
> Another question. According to my understanding,
> When exporting and importing for defragmentation, if the
> table is not explicitly created with a custom set of
> storage parameters, it will take its parameters from the
> DDL in the export dump file. This is undesirable. Thus
> one *must* have means of creating the empty table before
> reimporting, to get true control over fragmentation (?).
>
> Automating this would have to be a matter of appropriate
> scripts/tools. One would need to be able to define all
> dependent objects, disable triggers before the data import,
> properly allocate the index to appropriate tablespaces with
> similiar concern to storage settings, etc.
>
> This subverts default settings that may come from tablespace
> definitions.
>
> Please correct me where I am wrong.
> :-)
>
> Akshay Jain
> _______________________
> Newcourt-CIT
> Tel. (416) 507-5385
> mailto:Akshay.Jain_at_cit.com
> _______________________
>
>
> -----Original Message-----
> Sent: Wednesday, April 26, 2000 11:53 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Why would you see an increase in fragmentation?
>
> You may see more fragments in the free space, but
> they will be coalescable.
>
> Part of Oracle's extent allocation algorithm is to
> coalesce free space as needed.
>
> Whether you coalesce it up front, or wait until you
> actually need it, the effects on fragmentation
> will be the same.
>
> Jared
>
> On Tue, 25 Apr 2000, Gregorio Ortiz wrote:
>
> > boys||girls
> >
> > Effect of pct_increase 0
> >
> > If one uses PCT_INCREASE 0, you will see an increase in fragmentation.
> I
> > usually use 1 in order for the tablespaces
> > to coalesce automatically via SMON. Unless you are proactive and
> manually
> > coalescing the tablespaces at regular intervals, you will be fine. By
> all
> > means, do not leave the default of 50%.
> >
> > /gxodba
> >
> > -----Original Message-----
> > From: bounce-oracle-102619_at_telelists.com
> > [mailto:bounce-oracle-102619_at_telelists.com]On Behalf Of Morton, Ronald D
> > Sent: Tuesday, April 25, 2000 3:21 PM
> > To: oracle list
> > Subject: RE: Tablespace reorg tools
> >
> >
> > Correct me if I'm wrong but I believe that the default value for
> PCTINCREASE
> > is 50.
> > You must specifically set it to zero if you want to have full control
> over
> > your extents.
> > I set all of mine to zero in the DEFAULT STORAGE clause when I create a
> > tablespace.
> >
> > HTH,
> > Ron
> >
> > > -----Original Message-----
> > > From: Lisa_Koivu_at_gelco.com [SMTP:Lisa_Koivu_at_gelco.com]
> > > Sent: Tuesday, April 25, 2000 11:21 AM
> > > To: oracle list
> > > Cc: oracle list
> > > Subject: Re: Tablespace reorg tools
> > >
> > > Ana, have you tried 'TRUNCATE TABLE DROP STORAGE' and removing the
> > > PCTINCREASE?
> > > I never use pctincrease, I want to specifically state the size of the
> > > extents.
> > > Others may disagree...
> > >
> > > I don't see why you would have to reorg if you are truncating.
> Truncating
> > > is
> > > the first step in reorging tables. I wish I could truncate some of my
> > > tables
> > > daily.
> > >
> > >
> > >
> > >
> > >
> > > achoto_at_american.edu on 04/25/2000 10:09:03 AM
> > >
> > > Please respond to achoto_at_american.edu
> > >
> > > To: "oracle list" <oracle_at_telelists.com>
> > > cc: (bcc: Lisa Koivu/GELCO)
> > >
> > > Subject: Tablespace reorg tools
> > >
> > >
> > >
> > > We're having problems with two tablespaces that have plenty of free
> space,
> > > but, Oracle doesn't seem to recognize it. We truncate some tables and
> > > reload them every night, but once in a while one or two large tables
> fail
> > > to load because the next available extent is not large enough. All of
> our
> > > tables have an initial extent of 81920, 50% increase, minimum extents
> 1,
> > > and maximum extents 1017. I suspect that the 50% increase may be the
> > > culprit. Do we need to look closely at this and maybe change it?
> > >
> > > We're on Oracle 8.0.5 in a Sun box. By looking at the tablespace map
> I
> > > can
> > > see that there appear to be a lot of fragmentation. There are many
> free
> > > blocks scattered in the middle, but everything to the top and bottom
> of
> > > the
> > > tablespace appear full.
> > >
> > > My question is, other than exporting, dropping the tables, and
> importing
> > > them, is there another way to reorganize the tables in the tablespace?
> > > We'd like to do this with as little disturbance to our users as
> possible.
> > >
> > > Are there any tools that someone out there in Oracleland can recommend
> to
> > > keep track of fragmentation and to reorganize tablespaces?
> > >
> > > Any light that someone in this list can shed will be greatly
> appreciated.
> > >
> > > Thanks
> > >
> > > Ana E. Choto
> > > Systems Programmer
> > > American University
> > > Office of Information Technology
> > > Phone (202) 885-2275
> > > Fax (202) 885-2224
> > >
> > >
> > > ---
> > > You are currently subscribed to oracle as: lisa_koivu_at_gelco.com
> > > To unsubscribe send a blank email to
> leave-oracle-119953R_at_telelists.com
> > > or visit the Oracle mailing list on the Web at:
> > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ---
> > > You are currently subscribed to oracle as: rdmorton_at_switch.com
> > > To unsubscribe send a blank email to leave-oracle-76034V_at_telelists.com
> > > or visit the Oracle mailing list on the Web at:
> > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
> > ---
> > You are currently subscribed to oracle as: gortiz_at_houston.omnes.slb.com
> > To unsubscribe send a blank email to leave-oracle-102619X_at_telelists.com
> > or visit the Oracle mailing list on the Web at:
> > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
> >
> > ---
> > You are currently subscribed to oracle as: jkstill_at_bcbso.com
> > To unsubscribe send a blank email to leave-oracle-20875Y_at_telelists.com
> > or visit the Oracle mailing list on the Web at:
> > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --
> Author: Jared Still
> INET: jkstill_at_bcbso.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).
> --
> Author: Jain, Akshay
> INET: Akshay.Jain_at_cit.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).
The information in this Internet e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.
When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.
Received on Thu Apr 27 2000 - 05:02:57 CDT
