| 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: Locally Managed Tablespaces
Locally managed tablespaces have been presented as a panacea, and I'm
using them in our test 8.1.6 database; however, I've seen no mention of
the DBMS_SPACE_ADMIN package. Has anyone had to use this, other than
for verification. If so why, and did it work? If you are verifying your
segments & bitmaps, how often are you doing it; daily, or at a greater
frequency. For those unfamiliar with the package here are its members:
SEGMENT_VERIFY_EXTENTS
Verifies that the space owned by segment is
appropriately reflected in the bitmap as
used.
Verifies that the space owned by segment is
appropriately reflected in the bitmap as used
and that no other segment claims any of this
space to be used by it.
Marks a temporary segment as corrupt whereby
facilitating its elimination from the
dictionary (without space reclamation).
SEGMENT_MARK_VALID
Marks a corrupt temporary segment as valid. It
is useful when the corruption in the
segment extent map or elsewhere has been
resolved and the segment can be dropped
normally.
Dumps the extent map for a given segment.
TABLESPACE_VERIFY_
Verifies the bitmap of the tablespace with
extent maps of the segments in that tablespace
to make sure everything is consistent.
TABLESPACE_EXTENT_MAKE_
Makes this range (extent) of space free in the
bitmaps.
Makes this range (extent) of space used in the
bitmaps.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.tanford.edu
Jared Still wrote:
>
> Certainly simplifies my life.
>
> Jared
>
> On Wed, 26 Apr 2000, Miller, Jay wrote:
>
> > And of course if you use Uniform Extent sizing you don't have to worry about
> > coalescing at all:
> >
> > Original paper:
> > http://www.oracle.com/openworld/white_papers/711.pdf
> >
> >
> > More up to date/revised version:
> > http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
> >
> > -----Original Message-----
> > Sent: Wednesday, April 26, 2000 5:07 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Jarad
> >
> > Even we have pct_increase = 0 ??
> > Is it oracle 8 feature??
> >
> > I believe 7.3.4 doesn't support this.
> >
> > Sameer
> >
> > -----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: Oak, Sameer
> > INET: Sameer.Oak_at_CWUSA.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: Miller, Jay
> > INET: JayMiller_at_TDWaterhouse.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).
> >
>
> 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
Received on Fri Apr 28 2000 - 10:14:53 CDT
 |
 |